SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Monday, October 30, 2006

Algorithm Question

SQL Apprentice Question
I have user table named TBL_USER which has userid and username fields.

I have another table named TBL_PRODUCT which need user info in it


Which way you offer me to follow .


1. Having Userid Field in TBL_Product
or
2. Having Username field in TBL_PRODUCT


to have relationship between 2 tables.


Could you explain which way i should follow and wht i should follow ?
You might give a web url ..



Celko Answers
First, stop using uppercase names that violate ISO-11179 rules. Those
prefixes are redundant, mess up the data dictionary.


>> I have user table named TBL_USER which has userid and username field [sic] <<


Columns are not fields. The source of your design problem is that you
do not know what a table, coluymn or row are. Let's try guess at the
proper DDL, since you did not bother to post anything:

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY
CHECK (<>,
user_name VARCHAR(35) NOT NULL,
etc);
.



>> I have another table named TBL_PRODUCT which need user info in it <<


NO! Why is a user attribute part of a set of products??

CREATE TABLE Products
(product_id INTEGER NOT NULL PRIMARY KEY
CHECK (<>,
product_name VARCHAR(35) NOT NULL,
etc.);
.



>> Which way you offer me to follow .. to have relationship between 2 tables. <<


Of course.

CREATE TABLE Purchases
(product_id INTEGER NOT NULL
REFERENCES Products(product_id)
ON UPDATE CASCADE
ON DELETE CASCADE, -- guess at the rules
user_id INTEGER NOT NULL
REFERENCES Users(user_id)
ON UPDATE CASCADE
ON DELETE CASCADE, -- guess at the rules
PRIMARY KEY (user_id, product_id),
etc.);



>> Could you explain which way I should follow and why i.. <<


Any book on basic data modeling will tell you. A table is not a file;
it represents a set of entities of the same kind and their attributes
or it models a relationship -- never both in one table.

No comments: