MySQL Forums
Forum List  »  Newbie

Re: Inserting data into related tables
Posted by: Peter Brawley
Date: September 07, 2022 11:13AM

Alin, before you try to design a database, familiarise yourself at least with the basics, eg ...

https://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch01.pdf

https://www.artfulsoftware.com/dbdesignbasics.html

... and work through some examples eg in the manual.

Quote

CREATE TABLE User(
userID int PRIMARY KEY AUTO_INCREMENT,
userName varchar(70) UNIQUE NOT NULL,
passwd VARCHAR(70) NOT NULL,
INDEX(userID, userName)
)

Auto_increment id columns should be int unsigned. The primary key is userid, username should not be unique eg there can be multiple John Smiths with different IDs, INDEX(userID, userName) is useless. And do not use Hungarian capitalisation on database object names. Keep them lower case.

Quote

CREATE TABLE Order(
orderID int PRIMARY KEY,
userReference, FOREIGN KEY(userReference) REFERENCES
User(userID),
userName1 VARCHAR(70) UNIQUE NOT NULL, FOREIGN KEY(userName1) REFERENCES
User(userName),
Product VARCHAR(70) NOT NULL,
INDEX(orderID, userName1)
)

Column names are best made self-documenting. userReference will be a copy of user.userid so is best named userid. Foreign Key must be a separate declaration. As Phil says, it is a design error to duplicate the username column in the orders table. And INDEX(orderID, userName1) is useless.

Options: ReplyQuote


Subject
Written By
Posted
September 06, 2022 11:02PM
Re: Inserting data into related tables
September 07, 2022 11:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.