My contribution to this is:
As you should implement names for the metadata, add a metadefinition
table:
meta_definition (metadefinition_ID(PK), name (varchar), datatype)
Then modify your meta_user
table
meta_user (meta_ID (PK), user_ID(FK), metadefinition_ID(FK))
You have 3 choices (A,B,C) or even more...
Variant A: Keep your design of storing the values for all possible datatypes in one single row resulting in a sparse table.
This is the easiest to implement but the ugliest in terms of 'clean design' (my opinion).
Variant B: Use distinct value tables per data type: Instead of having one sparse table meta_user
you could use 6 tables meta_number
, meta_decimal
, meta_string
. Each table has the form:
meta_XXXX (metadata_ID(PK), meta_ID(FK), value)
Imho, this is the cleanest design (but a bit complicated to work with).
Variant C: reduce meta_user
to hold three columns (I renamed it to meta_values
, as it hold values and not users).
meta_values (metavalue_ID(PK), meta_ID(FK), value (varchar))
Format all values as string/varchar and stuff them into the value
column. This is not well designed and a bad idea if you are going to use the values within SQL as you would have to do expensive and complicated casting in order to use the 'real' values.
This is imho the most compact design.
To list all metadata of a specific user, you can use
select u.name,
md.name as 'AttributeName',
md.DataType
from user u
join meta_user mu on u.user_ID = mu.userID
join meta_definition md on md.metadefinition_ID = mu. metadefinition_ID
selecting the values for a given user would be
Variant A:
select u.name,
md.name as 'AttributeName',
mv.* -- show all different data types
from user u
join meta_user mu on u.user_ID = mu.userID
join meta_definition md on md.metadefinition_ID = mu. metadefinition_ID
join meta_value mv on mv.meta_ID = mu.metaID
Disadvantage: When new datatypes are available, you would have to add a column, recompile the query and change your software as well.
select u.name,
md.name as 'AttributeName',
mnum.value as NumericValue,
mdec.value as DecimalValue
...
from user u
join meta_user mu on u.user_ID = mu.userID
join meta_definition md on md.metadefinition_ID = mu. metadefinition_ID
left join meta_numeric mnum on mnum.meta_ID = mu.metaID
left join meta_decimal mdec on mdec.meta_ID = mu.metaID
...
Disadvantage: Slow if many users and attributes are being stored. Needs a new table when a new datatype is being introduced.
Variant C:
select u.name,
md.name as 'AttributeName',
md.DataType -- client needs this to convert to original datatype
mv.value -- appears formatted as string
from user u
join meta_user mu on u.user_ID = mu.userID
join meta_definition md on md.metadefinition_ID = mu. metadefinition_ID
join meta_value mv on mv.meta_ID = mu.metaID
Advantage: Don't have to change the query in case new datatypes are being introduced.