SitePoint Sponsor |
|
User Tag List
Results 1 to 13 of 13
Thread: Is my schema okay ?
-
Jan 7, 2005, 08:02 #1
- Join Date
- Apr 2004
- Location
- teesside
- Posts
- 168
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Is my schema okay ?
Hi Guys
Ive created my model and interpreted it into a schema. I will be using Mysql.
Does it make sense?
Model:
Schema:
CREATE TABLE tblcustomer (
login_name VARCHAR(20) NOT NULL,
createDate DATE NOT NULL,
password VARCHAR(255) NOT NULL,
lastName VARCHAR(50) NULL,
firstName VARCHAR(50) NULL,
street VARCHAR(50) NULL,
town VARCHAR(50) NULL,
postcode CHAR(10) NULL,
email VARCHAR(50) NULL,
phone CHAR(15) NULL,
fax CHAR(15) NULL,
CONSTRAINT customer_key PRIMARY KEY (‘login_name’)
);
CREATE TABLE tbllogin (
login_time DATETIME NOT NULL,
login_name VARCHAR(20) NOT NULL,
CONSTRAINT login_key PRIMARY KEY (‘login_time’, ‘login_name’)
);
CREATE TABLE `tblproducts` (
`product_id` int(5) NOT NULL auto_increment,
`product_type_description` varchar(255) NOT NULL default '',
`product_name` varchar(50) NOT NULL default '',
`product_description` varchar(255) NOT NULL default 'no description at this time',
`product_price` decimal(9,2) NOT NULL default '0.00',
`product_image` varchar(15) NOT NULL default 'na.gif',
CONSTRAINT products_key PRIMARY KEY (‘product_id’),
CONSTRAINT holds FOREIGN KEY (‘product_type_description’) REFERENCES tblproduct_types (`product_type_description`)
);
CREATE TABLE tblproduct_types (
product_type_description VARCHAR(255) NOT NULL,
product_type_image CHAR(15) NULL,
CONSTRAINT productypes_key PRIMARY KEY (‘product_type_description’)
);
CREATE TABLE tblcustomer_product_interest (
login_name VARCHAR(20) NOT NULL,
product_type_description VARCHAR(255) NOT NULL,
CONSTRAINT produduct_interests_key PRIMARY KEY (‘login_name’, ‘product_type_description’)
);
CREATE TABLE tblcustomer_product_inspection(
datetime_inspect DATETIME NOT NULL,
login_name VARCHAR(20) NOT NULL,
product_id INT(5) NOT NULL auto_increment,
CONSTRAINT product_inspection_key PRIMARY KEY (‘datetime_inspect’,’ login_name’,’ product_id’)
);
-
Jan 7, 2005, 09:08 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
ditch the ‘microsoft word curly quotes’
since this is for a school project, you will probably want to define all your foreign keys, even though mysql doesn't support them (it will still accept and parse the syntax for them)
you may also want to reconsider the use of the "tbl" prefix in your table names -- it's redundant and irritating
after all, you don't say --
CREATE TABLE tblcustomer (
columnlogin_name VARCHAR(20) NOT NULL,
columncreateDate DATE NOT NULL,
columnpassword VARCHAR(255) NOT NULL,
columnlastName VARCHAR(50) NULL,
columnfirstName VARCHAR(50) NULL,
columnstreet VARCHAR(50) NULL,
columntown VARCHAR(50) NULL,
columnpostcode CHAR(10) NULL,
columnemail VARCHAR(50) NULL,
columnphone CHAR(15) NULL,
columnfax CHAR(15) NULL,
CONSTRAINT constraint_customer_key PRIMARY KEY (columnlogin_name)
);
-
Jan 7, 2005, 09:27 #3
- Join Date
- Apr 2004
- Location
- teesside
- Posts
- 168
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
since this is for a school project, you will probably want to define all your foreign keys, even though mysql doesn't support them (it will still accept and parse the syntax for them)
Cheers
Best Wishes
Richard
-
Jan 7, 2005, 09:32 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
okay, here is an example
you have defined the FK product_type_description in the tblproducts table
you have not defined the FK login_name in the tblcustomer_product_interest table
-
Jan 7, 2005, 10:09 #5
- Join Date
- Apr 2004
- Location
- teesside
- Posts
- 168
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
CREATE TABLE customer (
login_name VARCHAR(20) NOT NULL,
createDate DATE NOT NULL,
password VARCHAR(255) NOT NULL,
lastName VARCHAR(50) NULL,
firstName VARCHAR(50) NULL,
street VARCHAR(50) NULL,
town VARCHAR(50) NULL,
postcode CHAR(10) NULL,
email VARCHAR(50) NULL,
phone CHAR(15) NULL,
fax CHAR(15) NULL,
CONSTRAINT customer_key PRIMARY KEY (login_name)
);
CREATE TABLE login (
login_time DATETIME NOT NULL,
login_name VARCHAR(20) NOT NULL,
CONSTRAINT login_key PRIMARY KEY (login_time, login_name),
CONSTRAINT holds FOREIGN KEY (login_name) REFERENCES customer (login_name)
);
CREATE TABLE products (
product_id INT(5) NOT NULL auto_increment,
product_type_description VARCHAR(255) NOT NULL default '',
product_name VARCHAR(50) NOT NULL default '',
product_description VARCHAR(255) NOT NULL default 'no description at this time',
product_price DECIMAL(9,2) NOT NULL default '0.00',
product_image VARCHAR (15) NOT NULL default 'na.gif',
CONSTRAINT products_key PRIMARY KEY (product_id),
CONSTRAINT holds FOREIGN KEY (product_type_description) REFERENCES product_types (product_type_description)
);
CREATE TABLE product_types (
product_type_description VARCHAR(255) NOT NULL,
product_type_image CHAR(15) NULL,
CONSTRAINT productypes_key PRIMARY KEY (product_type_description)
);
CREATE TABLE customer_product_interest (
login_name VARCHAR(20) NOT NULL,
product_type_description VARCHAR(255) NOT NULL,
CONSTRAINT produduct_interests_key PRIMARY KEY (login_name, product_type_description),
CONSTRAINT holds FOREIGN KEY (login_name) REFERENCES customer (login_name),
CONSTRAINT holds FOREIGN KEY (product_type_description) REFERENCES product_types (product_type_description)
);
CREATE TABLE customer_product_inspection (
datetime_inspect DATETIME NOT NULL,
login_name VARCHAR(20) NOT NULL,
product_id INT(5) NOT NULL auto_increment,
CONSTRAINT product_inspection_key PRIMARY KEY (datetime_inspect, login_name, product_id),
CONSTRAINT holds FOREIGN KEY (login_name) REFERENCES customer (login_name),
CONSTRAINT holds FOREIGN KEY (product_id) REFERENCES products (product_id)
);
-
Jan 7, 2005, 10:20 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you may run into difficulty with all those identically-named constraints
yes, i realize it's probably a result of hasty cuttin' and pastin'
but you must really slow down and try to do this on your own
are you testing any of this in mysql as you go along?
surely you do not want to rely on us for absolutely everything
-
Jan 7, 2005, 10:26 #7
- Join Date
- Apr 2004
- Location
- teesside
- Posts
- 168
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
Do you mean for example.
CONSTRAINT holds FOREIGN KEY (product_id) REFERENCES products (product_id)
Is this what you mean?
Yes i am running it through mysql as i go along
-
Jan 16, 2005, 10:46 #8
- Join Date
- Jan 2004
- Location
- London, Ontario
- Posts
- 7
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
You should have a customer_id field and use it as your primary key in the customer table (and foreign key in the other tables). Its always quicker for look up and sorting if you are using an number field then a text field. Also, a customer may want to change their login_name (which is perfectly resonable) and you wouldn't be able to with how its setup now. Well you could, but it would require massive data changes to occur and thats not a good way to operate a db.
-
Jan 16, 2005, 11:04 #9
- Join Date
- Apr 2004
- Location
- teesside
- Posts
- 168
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by Rivux
do you think i should keep the login name as a primary key and have a customer id as a primary key also?
i am using mysql
cheers
-
Jan 16, 2005, 12:35 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you can have only one primary key per table
-
Jan 16, 2005, 17:20 #11
- Join Date
- Apr 2004
- Location
- teesside
- Posts
- 168
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by r937
my schema states otherwise.
-
Jan 16, 2005, 17:37 #12
- Join Date
- Aug 2000
- Location
- Houston, TX, USA
- Posts
- 6,455
- Mentioned
- 11 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by weewizard
ssegraves [at] gmail.com
On Image Use, Abuse, and Where We're Headed
stephan | XMLHttpRequest Basics
flickr | last.fm | Cogentas, LLC
-
Jan 16, 2005, 17:45 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
thanks stephan
Bookmarks