Does my database design meet real-world standard?
I read a lot, watch tutorial a lot, practice, create the first database and now I get lost. I don't know if it is a good practice or a real-world good example of database design as practice by database professional.
I created a database below that I realize that its difficult in some tables to avoid duplication, for example, in table addresses, if husband and wife register, both of them have similar address. The duplication cannot be avoid in table visitors (first_name or last_name), in table agents (reputation), in table listings (bedroom, bathroom, car) as well.
Here is the code,
DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;
CREATE TABLE visitors(
visitors_id INT (12) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR (15) NOT NULL,
mobile_number VARCHAR (20) NOT NULL,
nic VARCHAR (30) NOT NULL,
nic_image VARCHAR (30) NOT NULL,
profile_image VARCHAR (30) NOT NULL,
PRIMARY KEY(visitors_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE positions(
positions_id VARCHAR (4) NOT NULL,
visitors_id INT (12) NOT NULL,
positions_name VARCHAR (14) NOT NULL,
PRIMARY KEY(positions_id),
FOREIGN KEY(visitors_id) REFERENCES visitors(visitors_id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE agents(
agents_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
bio TEXT,
reputation DECIMAL(2,1) NOT NULL,
PRIMARY KEY(agents_id),
FOREIGN KEY(positions_id) REFERENCES positions(positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE customers(
customers_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
email VARCHAR (30) NOT NULL,
PRIMARY KEY(customers_id),
FOREIGN KEY(positions_id) REFERENCES positions(positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE companies(
companies_id INT (20) NOT NULL,
brand VARCHAR(30) NOT NULL,
register_name VARCHAR (30) NOT NULL,
registration_number VARCHAR (30) NOT NULL,
phone_number VARCHAR (26) NOT NULL,
PRIMARY KEY(companies_id),
FOREIGN KEY(companies_id) REFERENCES agents(agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE addresses(
addresses_id INT (12) NOT NULL AUTO_INCREMENT,
agents_id INT (20) NOT NULL,
companies_id INT (20) NOT NULL,
address VARCHAR (15) NOT NULL,
address2 VARCHAR (20) NOT NULL,
PRIMARY KEY(addresses_id),
FOREIGN KEY(agents_id) REFERENCES agents(agents_id),
FOREIGN KEY(companies_id) REFERENCES companies(companies_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE listings(
listings_id VARCHAR(12) NOT NULL,
agents_id INT (20) NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL,
location CHAR (20) NOT NULL,
bedroom INT (4) NOT NULL,
bathroom INT (4) NOT NULL,
garage INT (4) NOT Null,
image1 VARCHAR (30),
image2 VARCHAR (30),
image3 VARCHAR (30),
image4 VARCHAR (30),
PRIMARY KEY(listings_id),
FOREIGN KEY(agents_id) REFERENCES agents(agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
SELECT * FROM agents JOIN companies ON agents.agents_id = companies.companies_id
The relationship that I expected is explain below
visitors <-> positions ONE TO MANY
One visitor can only have one position and one position can has many visitors.
positions <-> customers ONE TO MANY
One position can has many customers and one customer can have only one position.
positions <-> agents ONE TO MANY
One position can has many agents and one agent can have only one position
agents <-> company ONE TO ONE
One agent can be registered in only one company and one company can have many agents but only have one UNIQUE agent. For example, if an agent whose id is 123 (UNIQUE) already registered as member of company Y, the agent cannot register as member of company Z. This is to make sure if another company register this agent, the database will reject it.
agents <-> address ONE TO MANY
One agents can has many addresses one address only belongs to an agent
agents <-> listings ONE TO MANY
One agents can has many listings and one listing only belongs to an agent
My questions:
- Is it a good practice database design?
- What kind of normalization can be satisfied?
- Do you find a weakness of relationship as I explain such query problem?
- How can I improve this design to meet the real-world database standard (if any)
If you have any questions please ask in plain English that is easy to understand.
mysql database-design normalization
New contributor
add a comment |
I read a lot, watch tutorial a lot, practice, create the first database and now I get lost. I don't know if it is a good practice or a real-world good example of database design as practice by database professional.
I created a database below that I realize that its difficult in some tables to avoid duplication, for example, in table addresses, if husband and wife register, both of them have similar address. The duplication cannot be avoid in table visitors (first_name or last_name), in table agents (reputation), in table listings (bedroom, bathroom, car) as well.
Here is the code,
DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;
CREATE TABLE visitors(
visitors_id INT (12) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR (15) NOT NULL,
mobile_number VARCHAR (20) NOT NULL,
nic VARCHAR (30) NOT NULL,
nic_image VARCHAR (30) NOT NULL,
profile_image VARCHAR (30) NOT NULL,
PRIMARY KEY(visitors_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE positions(
positions_id VARCHAR (4) NOT NULL,
visitors_id INT (12) NOT NULL,
positions_name VARCHAR (14) NOT NULL,
PRIMARY KEY(positions_id),
FOREIGN KEY(visitors_id) REFERENCES visitors(visitors_id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE agents(
agents_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
bio TEXT,
reputation DECIMAL(2,1) NOT NULL,
PRIMARY KEY(agents_id),
FOREIGN KEY(positions_id) REFERENCES positions(positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE customers(
customers_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
email VARCHAR (30) NOT NULL,
PRIMARY KEY(customers_id),
FOREIGN KEY(positions_id) REFERENCES positions(positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE companies(
companies_id INT (20) NOT NULL,
brand VARCHAR(30) NOT NULL,
register_name VARCHAR (30) NOT NULL,
registration_number VARCHAR (30) NOT NULL,
phone_number VARCHAR (26) NOT NULL,
PRIMARY KEY(companies_id),
FOREIGN KEY(companies_id) REFERENCES agents(agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE addresses(
addresses_id INT (12) NOT NULL AUTO_INCREMENT,
agents_id INT (20) NOT NULL,
companies_id INT (20) NOT NULL,
address VARCHAR (15) NOT NULL,
address2 VARCHAR (20) NOT NULL,
PRIMARY KEY(addresses_id),
FOREIGN KEY(agents_id) REFERENCES agents(agents_id),
FOREIGN KEY(companies_id) REFERENCES companies(companies_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE listings(
listings_id VARCHAR(12) NOT NULL,
agents_id INT (20) NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL,
location CHAR (20) NOT NULL,
bedroom INT (4) NOT NULL,
bathroom INT (4) NOT NULL,
garage INT (4) NOT Null,
image1 VARCHAR (30),
image2 VARCHAR (30),
image3 VARCHAR (30),
image4 VARCHAR (30),
PRIMARY KEY(listings_id),
FOREIGN KEY(agents_id) REFERENCES agents(agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
SELECT * FROM agents JOIN companies ON agents.agents_id = companies.companies_id
The relationship that I expected is explain below
visitors <-> positions ONE TO MANY
One visitor can only have one position and one position can has many visitors.
positions <-> customers ONE TO MANY
One position can has many customers and one customer can have only one position.
positions <-> agents ONE TO MANY
One position can has many agents and one agent can have only one position
agents <-> company ONE TO ONE
One agent can be registered in only one company and one company can have many agents but only have one UNIQUE agent. For example, if an agent whose id is 123 (UNIQUE) already registered as member of company Y, the agent cannot register as member of company Z. This is to make sure if another company register this agent, the database will reject it.
agents <-> address ONE TO MANY
One agents can has many addresses one address only belongs to an agent
agents <-> listings ONE TO MANY
One agents can has many listings and one listing only belongs to an agent
My questions:
- Is it a good practice database design?
- What kind of normalization can be satisfied?
- Do you find a weakness of relationship as I explain such query problem?
- How can I improve this design to meet the real-world database standard (if any)
If you have any questions please ask in plain English that is easy to understand.
mysql database-design normalization
New contributor
add a comment |
I read a lot, watch tutorial a lot, practice, create the first database and now I get lost. I don't know if it is a good practice or a real-world good example of database design as practice by database professional.
I created a database below that I realize that its difficult in some tables to avoid duplication, for example, in table addresses, if husband and wife register, both of them have similar address. The duplication cannot be avoid in table visitors (first_name or last_name), in table agents (reputation), in table listings (bedroom, bathroom, car) as well.
Here is the code,
DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;
CREATE TABLE visitors(
visitors_id INT (12) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR (15) NOT NULL,
mobile_number VARCHAR (20) NOT NULL,
nic VARCHAR (30) NOT NULL,
nic_image VARCHAR (30) NOT NULL,
profile_image VARCHAR (30) NOT NULL,
PRIMARY KEY(visitors_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE positions(
positions_id VARCHAR (4) NOT NULL,
visitors_id INT (12) NOT NULL,
positions_name VARCHAR (14) NOT NULL,
PRIMARY KEY(positions_id),
FOREIGN KEY(visitors_id) REFERENCES visitors(visitors_id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE agents(
agents_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
bio TEXT,
reputation DECIMAL(2,1) NOT NULL,
PRIMARY KEY(agents_id),
FOREIGN KEY(positions_id) REFERENCES positions(positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE customers(
customers_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
email VARCHAR (30) NOT NULL,
PRIMARY KEY(customers_id),
FOREIGN KEY(positions_id) REFERENCES positions(positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE companies(
companies_id INT (20) NOT NULL,
brand VARCHAR(30) NOT NULL,
register_name VARCHAR (30) NOT NULL,
registration_number VARCHAR (30) NOT NULL,
phone_number VARCHAR (26) NOT NULL,
PRIMARY KEY(companies_id),
FOREIGN KEY(companies_id) REFERENCES agents(agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE addresses(
addresses_id INT (12) NOT NULL AUTO_INCREMENT,
agents_id INT (20) NOT NULL,
companies_id INT (20) NOT NULL,
address VARCHAR (15) NOT NULL,
address2 VARCHAR (20) NOT NULL,
PRIMARY KEY(addresses_id),
FOREIGN KEY(agents_id) REFERENCES agents(agents_id),
FOREIGN KEY(companies_id) REFERENCES companies(companies_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE listings(
listings_id VARCHAR(12) NOT NULL,
agents_id INT (20) NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL,
location CHAR (20) NOT NULL,
bedroom INT (4) NOT NULL,
bathroom INT (4) NOT NULL,
garage INT (4) NOT Null,
image1 VARCHAR (30),
image2 VARCHAR (30),
image3 VARCHAR (30),
image4 VARCHAR (30),
PRIMARY KEY(listings_id),
FOREIGN KEY(agents_id) REFERENCES agents(agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
SELECT * FROM agents JOIN companies ON agents.agents_id = companies.companies_id
The relationship that I expected is explain below
visitors <-> positions ONE TO MANY
One visitor can only have one position and one position can has many visitors.
positions <-> customers ONE TO MANY
One position can has many customers and one customer can have only one position.
positions <-> agents ONE TO MANY
One position can has many agents and one agent can have only one position
agents <-> company ONE TO ONE
One agent can be registered in only one company and one company can have many agents but only have one UNIQUE agent. For example, if an agent whose id is 123 (UNIQUE) already registered as member of company Y, the agent cannot register as member of company Z. This is to make sure if another company register this agent, the database will reject it.
agents <-> address ONE TO MANY
One agents can has many addresses one address only belongs to an agent
agents <-> listings ONE TO MANY
One agents can has many listings and one listing only belongs to an agent
My questions:
- Is it a good practice database design?
- What kind of normalization can be satisfied?
- Do you find a weakness of relationship as I explain such query problem?
- How can I improve this design to meet the real-world database standard (if any)
If you have any questions please ask in plain English that is easy to understand.
mysql database-design normalization
New contributor
I read a lot, watch tutorial a lot, practice, create the first database and now I get lost. I don't know if it is a good practice or a real-world good example of database design as practice by database professional.
I created a database below that I realize that its difficult in some tables to avoid duplication, for example, in table addresses, if husband and wife register, both of them have similar address. The duplication cannot be avoid in table visitors (first_name or last_name), in table agents (reputation), in table listings (bedroom, bathroom, car) as well.
Here is the code,
DROP DATABASE IF EXISTS test;
CREATE DATABASE test CHARSET = utf8 COLLATE = utf8_general_ci;
USE test;
CREATE TABLE visitors(
visitors_id INT (12) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR (15) NOT NULL,
mobile_number VARCHAR (20) NOT NULL,
nic VARCHAR (30) NOT NULL,
nic_image VARCHAR (30) NOT NULL,
profile_image VARCHAR (30) NOT NULL,
PRIMARY KEY(visitors_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE positions(
positions_id VARCHAR (4) NOT NULL,
visitors_id INT (12) NOT NULL,
positions_name VARCHAR (14) NOT NULL,
PRIMARY KEY(positions_id),
FOREIGN KEY(visitors_id) REFERENCES visitors(visitors_id)
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE agents(
agents_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
bio TEXT,
reputation DECIMAL(2,1) NOT NULL,
PRIMARY KEY(agents_id),
FOREIGN KEY(positions_id) REFERENCES positions(positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE customers(
customers_id INT (20) NOT NULL,
positions_id VARCHAR (4) NOT NULL,
email VARCHAR (30) NOT NULL,
PRIMARY KEY(customers_id),
FOREIGN KEY(positions_id) REFERENCES positions(positions_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE companies(
companies_id INT (20) NOT NULL,
brand VARCHAR(30) NOT NULL,
register_name VARCHAR (30) NOT NULL,
registration_number VARCHAR (30) NOT NULL,
phone_number VARCHAR (26) NOT NULL,
PRIMARY KEY(companies_id),
FOREIGN KEY(companies_id) REFERENCES agents(agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE addresses(
addresses_id INT (12) NOT NULL AUTO_INCREMENT,
agents_id INT (20) NOT NULL,
companies_id INT (20) NOT NULL,
address VARCHAR (15) NOT NULL,
address2 VARCHAR (20) NOT NULL,
PRIMARY KEY(addresses_id),
FOREIGN KEY(agents_id) REFERENCES agents(agents_id),
FOREIGN KEY(companies_id) REFERENCES companies(companies_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
CREATE TABLE listings(
listings_id VARCHAR(12) NOT NULL,
agents_id INT (20) NOT NULL,
title TEXT NOT NULL,
description TEXT NOT NULL,
location CHAR (20) NOT NULL,
bedroom INT (4) NOT NULL,
bathroom INT (4) NOT NULL,
garage INT (4) NOT Null,
image1 VARCHAR (30),
image2 VARCHAR (30),
image3 VARCHAR (30),
image4 VARCHAR (30),
PRIMARY KEY(listings_id),
FOREIGN KEY(agents_id) REFERENCES agents(agents_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;
SELECT * FROM agents JOIN companies ON agents.agents_id = companies.companies_id
The relationship that I expected is explain below
visitors <-> positions ONE TO MANY
One visitor can only have one position and one position can has many visitors.
positions <-> customers ONE TO MANY
One position can has many customers and one customer can have only one position.
positions <-> agents ONE TO MANY
One position can has many agents and one agent can have only one position
agents <-> company ONE TO ONE
One agent can be registered in only one company and one company can have many agents but only have one UNIQUE agent. For example, if an agent whose id is 123 (UNIQUE) already registered as member of company Y, the agent cannot register as member of company Z. This is to make sure if another company register this agent, the database will reject it.
agents <-> address ONE TO MANY
One agents can has many addresses one address only belongs to an agent
agents <-> listings ONE TO MANY
One agents can has many listings and one listing only belongs to an agent
My questions:
- Is it a good practice database design?
- What kind of normalization can be satisfied?
- Do you find a weakness of relationship as I explain such query problem?
- How can I improve this design to meet the real-world database standard (if any)
If you have any questions please ask in plain English that is easy to understand.
mysql database-design normalization
mysql database-design normalization
New contributor
New contributor
New contributor
asked 4 mins ago
XelonoviaXelonovia
1
1
New contributor
New contributor
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Xelonovia is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232884%2fdoes-my-database-design-meet-real-world-standard%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Xelonovia is a new contributor. Be nice, and check out our Code of Conduct.
Xelonovia is a new contributor. Be nice, and check out our Code of Conduct.
Xelonovia is a new contributor. Be nice, and check out our Code of Conduct.
Xelonovia is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232884%2fdoes-my-database-design-meet-real-world-standard%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown