November 19, 2004 at 3:19 pm
I am planning to build a software and the framework decides on the amount of data a dataset will have by defining it in the data definitions.
(Very similar to XML)
This means that Eg: Creating a contacts table may have "First Name", "Last Name", "Address1" as three columns. Later on they may add "ZIP"
I am planning to store the data definition in one table.
RecordID DataSetName Name Type
AAA Contacts First Name tChar
AAB Contacts Last Name tChar
Now, comes the part where I am planning to store my actual data.
I am planning to have a table defined where it has an ID linked to the first table to identify the record and the type will point to the column where the data needs to be picked up
Eg: If AAA type says tChar I will pick up the value for tChar field of datatype char
Extending this, I will have one column for every datatype plus a RecordID to link back to the data defns.
Can any one tell me if this is the correct way to do this? I am asking this because I do not know if the space is going to be reserver in SQL whether there is data in the column or not? Also I would like to know if this makes SQL run slower after I have lots and lots of data in recordsets.
TIA,
Abhinav Kushalnagar
Abhinav Kushalnagar
kushalnagar.com
November 19, 2004 at 4:05 pm
Maybe it's just me on a Friday afternoon, but this schema is more complex than necessary. It would work. A consideration would be that you must use joins for everything. For example, if someone wanted to create a mailing list, using your design, it would be three joins before you reach the data - type table - ID table - data table (if I have the design right). The more joins the potential for slower access (depending on indexes, etc).
I just say this because I worked on a database that religiously used header and detail tables and was in 4th Normal Form. It took 8 joins to create a mailing list and that was a real problem for the users, since they did that every month.
Quand on parle du loup, on en voit la queue
November 19, 2004 at 4:18 pm
I know that it is a bit complicated. This is because I am building a framework rather than a traditional software. This means that my clients can choose what information they want to capture and store and not worry about compatability.
Eg: Software A can hold information J, K and L
Software B can hold information B, N and M
I would like to avoid these issues. This means that they can change what they want to capture thereby I can not have a traditional normalized table either.
Also I found that searching is the best when it comes to this design. Depending on the output I can build a resultant RecordID column and then display all results belonging to that Record Search Result.
Any thoughts?
TIA
Abhinav Kushalnagar
kushalnagar.com
November 19, 2004 at 5:07 pm
Of course the more columns you add the slower the system will run. It is a nice whay to try and cosolidate functionality to allow end users to add columns but the lieklyhood of running into data consitiancy issue later is far reasching and the more data you have the higher the impact will be to get at the data. I will say this is a nice concept but is not a very effective or even practicl method. Might want to consider having changes coded to add columns to proper structured tables. The self service method even then has major risks of bad user input adding things like state which would be a better lookup table and really needs to be constrianed. You could still make something to do that for you based on user input but your risk here is too high especially for a production system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply