[thelist] [SQL] Can I Do This?

[Carolyn Jewel]

1) There are over 20 distinct values for subjects, with the likelihood
for there being added a lot more, so I fear an approach like this would
have problems scaling.

2) There is a strong potential of subjects being added/deleted/changed
without my knowing, which could break the query without me initially
being aware.

******

My apologies if you've already thought of this and dealt with it. You
originally described the subject table as a look up table with a PK that
resides in the Problem table (or whatever you called it) as a FK. If
you're not enforcing referential integrity (at all or properly for your
application) and users can delete subjects, you're going to orphan
subject records in the problem table. 

Another issue is changing the values in the subject table. Suppose that
on day 1 subjectA="Goldfish died" and there are 3 problems with dead
goldfish on day 2, all entered in the proper tables. On day 3 someone
updates SubjectA to "Wet Newspaper" and 6 people have that problem. Your
dead goldfish records are now lost and your Wet Newspaper problem looks
worse than it is.

Yet another issue arises with adding subject values. Suppose that on day
1 SubjectA="Dead Goldfish" and on day 2 someone adds "SubjectB="Goldfish
died" and SubjectC="gldfish dead." The problem should be fairly obvious
when you try to report on your data. And, depending, maybe you also end
up with SubjectD="Dead Goldfish" It's to be hoped that in addition to a
PK on an identity column, you've set a unique index on the text of the
subject. The point I'm getting around to is that applications need to
exercise caution with respect to adding, deleting and editing values in
a look up table, particularly if users can add, update or delete with
reckless abandon.

My experience has been that look up tables that store lots and lots of
values (depending, as always) are not always a great idea. If you have
1000 values in that table, you've placed a huge burden on the
application and the user to find and select the correct value and, in
fact, encouraged her to instead add what she needs rather than select
the correct value.

There are several database design approaches to address this issue, as
well as application solutions. Typically, you need a combination.
Depending on how you've resolved these tensions, your query could become
quite ugly and, of course, your data meaningless.

Carolyn J.
****This electronic mail message, and any attachments transmitted with it, contains confidential information, intended only for the named addressee(s). If you are not the intended recipient, or a person responsible for delivering this e-mail to the intended recipient, you are hereby notified that the use, distribution, copying, or disclosure of this communication is strictly prohibited. If you have received this e-mail in error, please immediately notify the sender by reply e-mail, and delete all copies of this communication from your computer and network. Thank you.*****