0

I've got a product I'm redesigning.

The old db design was ... ID, Key, Value, all strings, where the value could be a number, text, or a date. Trying to do type specific searches on this data was impossible(or slow enough that it didn't matter). Key is a set of values that an object has, so say obj a has a birthdate of 1/12/1969, name of Dan, and phoneNumber of 555-555-5555, i nthe database this was stored as

a birthdate 1/12/1969 a name Dan a phonenumber 555-555-5555

I want to redesign it so that the keys are actually fields so we would have this

a 1/12/1969 Dan 555-555-5555

The worry I have is that when the user goes in and adds a new Key, say cellphone, that adding the column could take a long time and/or be confusing to the user.

The users will most likely define the keys at the beginning, but there is a chance that a key gets added after years of using the product.

One thought I had was adding several columns of the different types we support to the table as empty and then renaming them as a user would add them, yes, I would still have the problem when I run out of these empty columns, but most of my users would never see this. I don't like this answer, but I like the alter table scenario somewhat less.

Anyone have any thoughts or ideas?

Finally, this is a product where the database backend can be oracle, sql server, or access(probably not, but maybe)

Dan OConnell
  • 217
  • 1
  • 8

4 Answers4

2

You can combine the two designs, take the time to really define the 95% that is actually needed in realtional tables. Then have an EAV table for the necessary customizable fields. Most customers will never add one if you have really done a good job of designing to include what will normally be needed.

Another alternative is to create a table with a set number of customizable fields that the user can define and then they will be limited to only 6 custom fields.

A third alternative is to have the custom field be in a separte table with a one to one relationship to the main table. This is joined to with a left join. You still have to change this structure when the customer adds a new field, but it should disrupt the other data less than if they add to the regular tables.

An XML data type, or large varchar field (such as varchar (max) in SQL Server) might be another possibility especially if you only want to display the custom data and not do any querying specifially on it.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1

What you're describing is called Entity Attribute Value. Modeling something like this is difficult and any solution you decide on will probably have at least some disadvantages. You should take a look at this stackoverflow question that has a pretty comprehensive discussion on the topic: Entity Attribute Value Database vs. strict Relational Model Ecommerce

Community
  • 1
  • 1
takteek
  • 7,020
  • 2
  • 39
  • 70
  • yep, became familiar with eav after starting at this company. I'm trying to get away from it, but running into the issue I'm sure led the original programmers to the use of eav, customer definable fields. I have to continue to allow the user to add fields ,but I want to make it as painless as possible – Dan OConnell Mar 23 '11 at 20:41
  • If this is a big part of your application maybe you would be better off with a key/value store database if it wouldn't be too hard to set something like that up. It's exactly what they were made for. – takteek Mar 23 '11 at 20:52
0

The Key/Value table design is total junk, so I am glad you're getting rid of it!

I would advise against adding extra columns to the tables for future expansion. Every field of every table should have a distinct interpretation, and should be constrained to hold only sensible values for that interpretation.

If you want to have user-designed custom fields, then a key/value system may suffice for this (and only this), with the understanding that queries and constraints on such user-designed data will be more difficult and inferior.

Jeffrey L Whitledge
  • 58,241
  • 9
  • 71
  • 99
  • I've got to have user defined custom fields(which is probably why the original programmer came up with the key/value, dont' know he's not here), it's at the heart of the rest of the product – Dan OConnell Mar 23 '11 at 20:27
0

you can map the keys to the oracle data type and use specific functions based on the data type while retrieving the values.

e.g. ID KEY DATATYPE VALUE a birthdate date 1/12/1969 a name string Dan a phonenumber string 555-555-5555

so you will have 3 rows for each user info, you can add more as you need. while retrieving u can use decode based on the DATATYPE value.

This method however may increase the space utilization.

j.i.t.h.e.s.h
  • 4,128
  • 2
  • 15
  • 7