Is my schema okay ?

[weewizard]

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’)
);

[r937]

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)
);

[weewizard]

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)

Can you give me an example of what you mean here?

Cheers

Best Wishes

Richard

[r937]

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

[weewizard]

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

Ammended Schema:

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)
);

[r937]

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

[weewizard]

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

Do you mean for example.

CONSTRAINT holds FOREIGN KEY (product_id) REFERENCES products (product_id)

Here I am using 'holds'.

Is this what you mean?

Yes i am running it through mysql as i go along

[Rivux]

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.

[weewizard]

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.

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

[r937]

you can have only one primary key per table

[weewizard]

you can have only one primary key per table

hi rudy

my schema states otherwise.

[Hartmann]

hi rudy

my schema states otherwise.

No, your schema states a composite primary key. It is only one key that comprises of multiple pieces, in this case the login name and the customer id.

[r937]

thanks stephan