-1

I am using mysql, I have a user table which holds some basic information like name, surname, age, gender, etc. I want to provide a feature called profile, where user will put other information like work location, alternative contact details, etc. These new columns are not fixed, in future there can me more columns. As of my knowledge there can be two approaches :

1) Add new columns every time when required, this will put Null entries to previous rows for newly added columns.

2) Add a new table which will contain all the columns ids to column names, and one more table which will contain key value like structure for each user who has the entry for that particular new column as follows :

Table1 : userId | firstName | age | ....

Table2 : columnId | columnName | dataType

Table3 : userId | columnId | value

Which would be the good approach for my scenario?

Prometheus
  • 549
  • 1
  • 7
  • 18
  • table 3 should be `userId | columnId | value` – Saic Siquot Jun 15 '15 at 11:01
  • Please decide what database you are actually using. – Gordon Linoff Jun 15 '15 at 11:01
  • keeping null values is not that bad... the answer depends on the frequency of your structure changes. however, for the second approach (should you choose to go with that) I would suggest a simply key-value table (perhaps with a data type column as well. – Zohar Peled Jun 15 '15 at 11:08

1 Answers1

4

It seems like a very valid question, just one with no right answer.

There are at least two more approaches not in your list:

  • Store the additional fields in a JSON or XML string within each record.
  • Add the columns in an auxiliary table, using the same primary key.

In order to decide which approach is best, you need to understand your problem a bit more. For instance:

  • How many profiles are there?
  • Are there performance considerations in adding new columns?
  • Will indexes be needed on the new columns?
  • Are the types of the new columns always the same?
  • Are foreign key references needed for the new columns?
  • Are other constraints needed or desirable?
  • Are all profiles intended to have all the columns?
  • Will the number of profile columns exceed the maximum number of columns for a table?

There is no one correct answer to this question. The EAV (entity-attribute-value data model) approach (your second approach) would typically have all the columns being the same type and preclude foreign key constraints (there are somewhat cumbersome ways around both these issues). On the other hand, if the number of profile columns exceeds the maximum for your database, then you might have no choice but to use some form of EAV.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Actually, there will be many profiles (at least 100M). Indexes will not be needed. All columns type will be different (but if we use second approach "EAV", it can be handled in code). Foreign key constraint is not needed. Previously created profile will also be having the newly created columns but with null value, until user does not fill the value. Number of columns will never exceed the max limit. – Prometheus Jun 15 '15 at 11:17
  • 1
    `it seems like a very valid question, just one with no right answer.` this alone deserves a +1. the rest of your answer deserves another +1. – Zohar Peled Jun 15 '15 at 11:22