| Bob Barrows [MVP] 2006-04-27, 7:52 am |
| Dan wrote:
> Yeah, i was trying to keep it short. I figured that any longer, then
> people wont read it. 
>
> The database has four tables. I'll pretend that the data were working
> with are "items".
>
> 1. items - about 10 fields. holds info like "itemID".
>
> 2. itemProperties - about 40 fields.
> itemID (to match with that of the 'items' table), - AUTO NUMBER
> 3 text fields, and everything else is a yes/no
>
> 3. itemMoreProperties - about 40 fields
> itemID (to match with that of the 'items' table)
> 5 text, and everything else "yes/no"
>
> 4. another table also related to items. assorted data types
>
>
> The class acts as a container. For a any item it can hold all of it's
> data.
>
> (in the most common scenario)
> the script creates a new class and populates it from the database. At
> this point we take any http posts, and compares with the value in the
> class. If it is different, then it is stored in the class, and the
> class puts in in the db.
The flaw with this design is that any time you need to add a new propery,
you have to modify the database structure, as well as all the code that
accesses this data. A better design would be:
boolPropertyNames
boolName - Text, PK -
used to control names used in BoolPropertyNames
itemBoolProperties:
itemID
boolName - Text containing the name of the property
foreign key to BoolPropertyNames table
boolValue - Yes/No
This will allow you to use
Select boolName from BoolPropertyNames
to get the boolean property names.
To add a new property, just add a record to boolPropertyNames (You should
probably strictly control the names that get entered here - I would not
allow users to create properties - that could lead to chaos). Nothing else
needs to change.
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
|