|
|
 | | From: | Terrence Brannon | | Subject: | Designing database tables to represent exclusion classes | | Date: | Fri, 21 Jan 2005 12:34:58 +0000 |
|
|
 |
Ok, I'm not sure if this is the best way to do what we are
doing. Let's say that we warehouse letters, words, and
sentences. And that we have a large word of clients who ask us to
serve them letters, words, and sentences.
Now, each client has the option of excluding up to 20 of each class of
data from us. Eg. client1 requests to never be served the letter z,
the words 4 and 6 and the sentence "art is good".
Now, my database structure is this:
CREATE TABLE client_exclusions (
client_id INTEGER NON NULL REFERENCES client,
exclusion_id INTEGER,
exclusion_type enum('letter','word','sentence')
PRIMARY KEY(client_id, exclusion_id, exclusion_type)
);
CREATE TABLE letter (
letter_id INTEGER NON NULL REFERENCES exclusion_id,
data VARCHAR(1)
);
CREATE TABLE word (
word_id INTEGER NON NULL REFERENCES exclusion_id,
data VARCHAR(80)
);
CREATE TABLE sentence (
sentence_id INTEGER NON NULL REFERENCES exclusion_id,
data VARCHAR(255)
);
So:
+ exclusion_id is not unique.
+ exclusion_id is a foreign key to one of the 3 tables letter,
word, sentence
+ when a client makes a request of us to serve a letter, word,
or sentence, we check the exclusion table for that
particular data type first and only serve a data item that
is not in that client's exclusion list.
Question:
+ do you think this database is designed properly? in
particular, the table client_exclusions?
Note:
+ the actual data types have been changed to letter, word and
sentence to preserve my job security :)
--
Carter's Compass: I know I'm on the right track when,
by deleting something, I'm adding functionality.
|
|
| | From: | andrewst at onetel.com | | Subject: | Re: Designing database tables to represent exclusion classes | | Date: | 21 Jan 2005 05:09:45 -0800 |
|
|
| Your design is definitely wrong at the moment.
1) If the tables Letter, Word and Sentence represent your "warehouse"
then they cannot and should not reference the client_exclusion table
(and they should of course have primary keys):
CREATE TABLE letters (
data VARCHAR(1) PRIMARY KEY
);
CREATE TABLE words (
data VARCHAR(80) PRIMARY KEY
);
CREATE TABLE sentences (
data VARCHAR(255) PRIMARY KEY
);
2) Why not have 3 matching exclusion tables?
CREATE TABLE client_word_exclusions (
client_id REFERENCES client,
data REFERENCES words
PRIMARY KEY(client_id, data)
);
....etc.
|
|
| | From: | Terrence Brannon | | Subject: | Re: Designing database tables to represent exclusion classes | | Date: | Fri, 21 Jan 2005 19:45:37 +0000 |
|
|
| andrewst@onetel.com writes:
> Your design is definitely wrong at the moment.
>
> 1) If the tables Letter, Word and Sentence represent your "warehouse"
> then they cannot and should not reference the client_exclusion table
oh, you are right. thank you.
>
> 2) Why not have 3 matching exclusion tables?
well, it does not seem as "parametric", meaning, instead of the
variance in exclusion being stored in a variable, it is hardcoded in
the table name.
normally, good programming practice tries to get all variance in
things selectable via a variable.
But I think you have a good point here as well. Thank you for your
input.
>
> CREATE TABLE client_word_exclusions (
> client_id REFERENCES client,
> data REFERENCES words
> PRIMARY KEY(client_id, data)
> );
>
> ...etc.
>
--
Carter's Compass: I know I'm on the right track when,
by deleting something, I'm adding functionality.
|
|
|
