November 13, 2007 at 11:22 pm
Hi Grant,
Not exactly sure what you mean by the many to many prevent it from having to store a row, would you just not store a value in the metadata table?
The way I built it last time was this:
Item -> MetaData
^
Column --|
The metadata column just stored the value, which column and which item it relates to. the column stores the column label for display and the item table stores the details about the item. The only reason I can see for a many to many is to reduce duplicate values? Or have I complete missed the point?
Thanks!
A
November 14, 2007 at 5:50 am
Well, let's say you have 30 different fields, all customizable by the user. This particular user only uses 5 of the fields, the other 25 lie fallow. When you insert the data, you'd only insert 5 rows for the 5 fields used and not even insert values for the other 25. Better still, sometimes, the 5 fields aren't used, they're nullable. So when only 3 of the 5 are filled out, you only store 3 rows. This radically reduces the amount of data you have to manipulate.
Rather than having 30 columns that are extremely parsely populated, you have 'x' number of rows for the exact data you need, providing for a very performant index. I've seen people take this approach and then store 30 rows which they'll go back & update or whatever. Again, don't store what isn't used. Write the queries such that they return all the column types, but only data where there is data.
Is that a bit more clear?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 14, 2007 at 6:45 pm
Hi Grant,
I think I understand what your saying. This is the design I came up with before:
I have four tables:
First one is called items:
ItemId, ItemName, DateAdded, etc.
99, "Cup", "2007/01/01", ...
I have a second table customers:
CustomerId, Name, Address, Etc
an example row would be: 12, "Cup company1", "115a blah street", ...
Third table CustomerMetaDataColumn:
Id, CustomerId, ColumnName, Order, Type.
an example row would be: 45, 12, "OwnerName", 1, int.
Forth table ItemMetaData
Id, ItemId, CustomerMetaDataColumn, MetaDataValue.
an example row would be: 1, 99, 45, "Jeff".
So if no column appears in the customer metadata for a certain column then no ItemMetaData can appear either. this elimates the need for the join table, how does that look?
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply