1

I am working on a design where I can have flexible attributes for users and I am confused how to continue the design of the schema.

I made a table where I kept system needed information:

Table name: users

  • id
  • username
  • password

Now, I wish to create a profile table and have one to one relation where all the other attributes in profile table such as email, first name, last name, etc. My question is: is there a way to add a third table in which profiles will be flexible? In other words, if my clients need to create a new attribute he/she won't need any customization to the code.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
Mo J. Mughrabi
  • 6,747
  • 16
  • 85
  • 143
  • 1
    why not just put all attributes in one table? one user has id, username, password, email, first name, etc – corroded May 30 '10 at 17:14
  • There's countless duplicates on SO: http://stackoverflow.com/questions/2932322/how-to-design-a-schema-where-the-columns-of-a-table-are-not-fixed – OMG Ponies May 30 '10 at 17:28

3 Answers3

1

You're looking for a normalized table. That is a table that has user_id, key, value columns which produce a 1:N relationship between User & this new table. Look into http://en.wikipedia.org/wiki/Database_normalization for a little more information. Performance isn't amazing with normalized tables and it can take some interesting planning for optimization of your code but it's a very standard practice.

Matt S
  • 1,767
  • 8
  • 9
1

Keep the fixed parts of the profile in a standard table to make it easy to query, add constraints, etc.

For the configurable parts it sounds like you are looking for an entity-attribute-value model. The extra configurability comes at a high cost though: everything will have to be stored as strings and you will have to do any data validation in the application, not in the database.

Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Read Joe Celko's comment on why to avoid EAV: http://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/ – marc_s May 30 '10 at 17:24
  • 2
    Are you claiming that EAV should never be used? See Bill Karwin's presentation http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back page 31. Now you may or may not think this this is an appopriate usage of EAV. If not, I'd like to hear why. But it is not true that it should never be used. – Mark Byers May 30 '10 at 17:37
  • I'm saying they're overused - too many times, one resorts to an EAV approach instead of really thinking about the proper structure. And lots of folks don't realize just how bad and tricky those constructs are to get any decent queries and performance out of them. So yes - try to avoid them whenever possible; 95% of the time, you can - if you try hard enough. – marc_s May 30 '10 at 20:16
  • @marc_s: I agree with you there, however in this case it seems to me to be a reasonable option (other options I can think of include: deny the feature request, or tell them to hire a programmer every time they need to make a change, or write code to dynamically modify the database when new profile fields are added and write very generic code that uses introspection on the database to generate the GUI - none of which are particularly appealing). If you know of a better way to solve his problem that avoids EAV I suggest that you post an alternative answer. I would certainly be interested. – Mark Byers May 30 '10 at 20:30
1

How will these attributes be used? Are they simply a bag of data or would the user expect that the system would do something with these values? Are there ever going to be any reports against them?

If the system must do something with these attributes then you should make them columns since code will have to be written anyway that does something special with the values. However, if the customers just want them to store data then an EAV might be the ticket.

If you are going to implement an EAV, I would suggest adding a DataType column to your attributes table. This enables you to do some rudimentary validation on the entered data and dynamically change the control used for entry.

If you are going to use an EAV, then the one rule you must follow is to never write any code where you specify a particular attribute. If these custom attributes are nothing more than a wad of data, then an EAV for this one portion of your system will work. You could even consider creating an XML column to store these attributes. SQL Server actually has an XML data type but all databases have some form of large text data type that will also work. On reports, the data would only ever be spit out. You would never place specific values in specific places on reports nor would you ever do any kind of numerical operation against the data.

The price of an EAV is vigilence and discipline. You have to have discipline amongst yourself and the other developers and especially report writers to never filter on a specific attribute no matter how much pressure you get from management. The moment a client wants to filter or do operations on a specific attribute, it must become a first class attribute as a column. If you feel that this kind of discipline cannot be maintained, then I would simply create columns for each attribute which would mean an adjustment to code but it will create less of mess down the road.

Thomas
  • 63,911
  • 12
  • 95
  • 141