What would be your data storage solution for a situation like this?

The easiest way to explain exactly what I mean is with an example. Imagine a Person table. If we have many different types of end users using this table, they may want to store different fields. Some will be the same across the board, like Username, first name, last name... But others will not be. Like Birthday. Some may want to store this information, and some may not.

Simply tacking the optional fields onto the table would lead to a very messy situation. So the Person table should only contain the information that is required and used across the board.

Birthday is a simple example which could be easy to solve. But there are other types of optional fields. Street addresses are a good example. A user should be able to store as many street addresses as they wish.

These examples have been open ended so far, the user defines values. But there are also situations where the user should only be able to select a value, and not necessarily enter their own. Gender is this type of situation. Another table would need to store the available values for gender (Male & Female), and then the optional field table would store the user’s selection if there is one.

There is another situation that could arise, an optional parameter that hasn’t been entered into the system. In this case, the user should be able to define what they want to store, and give it a value.

Thoughts?

lethologica wrote:

What would be your data storage solution for a situation like this?

I think this has been discussed here before. But, one solution to this would be to use an XML field to store the dynamic schema and data. You could also create an extention table that has a record for each field with an additional table that stores the type/validation rules for that field.

BOb

I think each case (field) should be treated (thought for) separatly.

Imagine a Person table. If we have many different types of end users using this table, they may want to store different fields. Some will be the same across the board, like Username, first name, last name... But others will not be.

If you have some well defined Person types, you may want to use the Person table as a main general table, then you may want to have another tables for each type that inherit from the Person table and have the extra fields defined within, an example would be customer table (that should have it's PK as a FK to the person table, and also defines some more fields which are specific to a customer entity)

Also you may allow null for certain fields in the Person table, if they don't belong to a certain subType, and yet they may have no value assigned to it. (A birthday is an example)

But there are other types of optional fields. Street addresses are a good example. A user should be able to store as many street addresses as they wish.

In this situation you should have another table to hold the Addresses, which should have a FK to the Person table or a subType of it. (1-m relation)

These examples have been open ended so far, the user defines values. But there are also situations where the user should only be able to select a value, and not necessarily enter their own. Gender is this type of situation. Another table would need to store the available values for gender (Male & Female), and then the optional field table would store the user’s selection if there is one.

Exactly, a lookUp table should be used in this situation.

There is another situation that could arise, an optional parameter that hasn’t been entered into the system. In this case, the user should be able to define what they want to store, and give it a value.

As pilotboba has suggested, an xml field would be possible here.