in reply to
Re: Database Design Issues - OT
in thread Database Design Issues - OT
If you have 270 products (rows?) and 10 categories, performance is not an issue, so a seperate table should be ok.
It's not just the amount of data that determines performance,
it's also the number and kind of transactions. If all do is
10 simple selects a day, I agree, performance is not an
issue. If you need to do 10,000 queries an hour, performance
is an issue, even if you have only 270
products. Then not doing the join can certainly be noticeble.
I've been a DBA in a previous job. My response to the original
question is "the article contains not enough information to
say whether the DBA is right or not". What (s)he says certainly
does have merit. But it depends on a lot of things whether
the performance gain is noticeble or not. How many selects?
How many updates? How many concurrent users accessing the
tables? How much cache? What else is going on? It requires
an awful lot of knowledge, not only from the strategies of
your database server but also from which transactions are
going to be performed to say which will give the best performance.
Perhaps only realistic testing can tell the difference.
And then you still need to make the trade-off between
performance and clean design. Note that the drawback (I could
make a typo when inserting something) can be overcome in
several ways. One could make an insert trigger that rolls
back the transaction if an illegal category is inserted.
Or one uses a list of constants in the code, or one makes
a layer between application(s) and the database that does the actual creation of SQL - and never
have applications touch the database directly. The layer
could exists of some libraries (modules), but it could also
exists of stored procedures. Or some combination.