[AccessD] The future of Access, .NET and SQL

Hi Arthur

Thanks, though these two violations make me feel sick - I thought that was what could be expected from FileMaker, Alpha Five, or similar.
Of course, you can (should) just deny to apply such "features" but it explains some of the weird things you see when web designers start designing databases.

/gustav

>>> artful at rogers.com 01-10-2005 04:42 >>>
The first violation, if you wish to call it that, is the ENUM column type.
This type specifies that the value must be one of a collection of enumerated
strings, such as "This", "That", "The other", "Nothing", etc. This is akin
to Access's value-list column definition.
The second violation is the SET column. Given a column definition such as 
MyColumn SET('one', 'two') NOT NULL 
Then MyColumn can have any of these values:
''
'one'
'two'
'one,two'
A SET can have a maximum of 64 different members.
Magic is being performed behind the scenes. You can do lightning-quick
queries against such columns, not surprisingly (note the 64-member maximum).

But the fact remains that this would make Dr. Codd turn over in his grave.
This sort of column violates everything we have learned about relational
database design. Columns should be ATOMIC -- one value per row/column.
My partner Peter Brawley and I have done some benchmarks and there is no
doubt about it, MySQL's set columns are fast. But what the benchmarks don't
mention is the cost of adding a new item to any given set. Suppose you have
1,000 installations. You have to restructure the table, then bump it out to
1.000 database servers. Not a pretty prospect. 
That said, the concept does work well for small sets that are unlikely to
change, such as "Potential Sexual Partners" set "male", "female", "tv",
"tg". That set covers the bases for the foreseeable future (unless I am
really old and really out of touch with such matters).
A.