August 22, 2006 at 7:26 am
What, if any, are some new data models for handling dynamic object properties. Example: The customer has a table with his client's data, but would like to in the future save other data about that client. At this time, the customer doesn't know what he wants to save about his client, so he builds a list of other attributes which become available to the client entry screen later. This is just an example, but I have the same type of scenario for many of my db objects. I want to build something that is still fast, yet robust enough that I do not have to add a column to the db (beyond what I original put into production) when a customer wants to begin storing the color of paint on the wall for each room in their space inventory list.
In the past, to accomplish this, I have created a not too relational table which references the table object, the unique pk id of that table, attribute name or pk, a datatype, custom value, etc.. I usually like to make one table for all objects, however this means I always have to have at least a dual composite key to make a reference to a table within SQL.
Any suggestions, ideas, tips, tricks, etc... If there is a better way, I'm all for changing direction.
Your input is very much appreciated, as is your time for reading this.
Sincerely,
M.Horton
Programmer, Texas University
August 22, 2006 at 8:26 am
If these are truly ad hoc, I've seen it done like you mention. With 2 child tables added to the main table. One would be a lookup for the "type" of item. Usually it's a FK to the parent along with it's own PK and a description field, like this
Create Table ChildAttributes
( MyID int
, ParentID int
, MyDescription varchar()
)
You could add a datatype to this as well. Then you have another child that PKs back to this and the parent with the values.
A two key PK isn't a bad thing, BTW.
August 22, 2006 at 11:01 am
Hey Thanks!
Yes, these are adhoc, but they will be somewhat standarized. I will most likely have a list of properties (attributes) which are assigned to a particular object. Once the user clicks an instance of that object, he should see a list of the attributes he can choose from. I'll definately need a datatype so I'll know which type of .Net control to render on the web, along with validation. This will be a major enhancement to a production system, enabling it to handle approved attributes by multiple departments. If you have some other suggestions, I'm really open minded. It's also a large project, so a bad design will stand out not only to my employer but also my peers. 🙂
Thanks again.
August 24, 2006 at 9:08 am
Joe
If I understand correctly, then each of the attributes will correspond to a column that the user can create dynamically. If you have a finite list of these, why not just create them all? Then you don't have to give the user DDL permissions, you can document your schema up front, it's more secure and less prone to data integrity issues, etc etc.
John
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply