What is the best way to implement a table with a variable number of columns and different types? (The number of columns is unknown)
We use a column of type XML. What do you think?
Add a comment
|
2 Answers
It sounds like using document based databases ,if possible.
Technically you can not have a variable column designed table in SQL-Server.
By the way having a table based on columns bellow, may help:
EntityName: Full qualified entity name
EntityIdentifier: Id
PropertyName:
PropertyType:
PropertyValue:
EntityStatus: may be useful on delete action
In these approach, like XML scenario you will need a parser to extract the entity. At list sql query will be more readable and updates will be more convenient.
Hope be helpful.
2 Comments
Ismail Sheidaei
Tahnks,please give me Your Mail to Send Complete Description My Emails: ESH_Computer110@yahoo Esmaeil From Shiraz-Iran
Mohsen Heydari
[email protected] is my email address, Its appreciated if the complete question and answer be negotiated on stack-overflow for others to use. by the way don't forget to mark the answer as the answer of your question, if fitting you.
Surely you would create another table where the key, is a composite key of the ID and ColumnName.
Table Def :
ID ColumnName ColumnValue
The Id is the key from the primary table with a FK to enforce integrity.
You now have two choices make the datatype of the value a dumping ground and insert anything, or create a table like the above for each datatype you need.
Then you can write a view with a pivot to make this look like a normal table for queries etc.
Ps I don't suggest this will be a) the best solution or b) the quickest.
2 Comments
Walter Mitty
Isn't this EAV? (for better or worse)
RubbleFord
I suppose it kinda is, hence my suggest's a) and b)