Hello, thanksmate:
At first instance, database normalization seems very difficult, but
once you catch it, it's very natural, your diagram, in MySQL is as
follows:
CREATE TABLE domain(
domain_id INT NOT NULL AUTO_INCREMENT,
domain_name VARCHAR(50) NOT NULL,
domain_type VARCHAR(20) NOT NULL,
PRIMARY KEY (domain_id),
UNIQUE UC_domain_id (domain_id));
CREATE TABLE notes(
domain_id INT,
notes TEXT,
FOREIGN KEY (domain_id) REFERENCES domain (domain_id));
CREATE TABLE money(
money_id INT NOT NULL AUTO_INCREMENT,
domain_id INT NOT NULL,
FOREIGN KEY (domain_id) REFERENCES domain (domain_id),
PRIMARY KEY (money_id),
UNIQUE UC_money_id (money_id));
CREATE TABLE purchased(
money_id INT,
purchased_date DATE NOT NULL,
purchased_price DECIMAL(9,2),
purchased_where VARCHAR(60),
FOREIGN KEY (money_id) REFERENCES money (money_id));
CREATE TABLE offers(
money_id INT,
offer_date DATE,
offer_price DECIMAL(9,2),
offer_who VARCHAR(60),
FOREIGN KEY (money_id) REFERENCES money (money_id));
CREATE TABLE sold(
money_id INT,
sold_date DATE,
sold_price DECIMAL(9,2),
sold_who VARCHAR(60),
FOREIGN KEY (money_id) REFERENCES money (money_id));
CREATE TABLE category(
category_id INT NOT NULL AUTO_INCREMENT,
domain_id INT NOT NULL,
category_art TINYINT,
category_business TINYINT,
category_entertainment TINYINT,
category_internet TINYINT,
category_services TINYINT,
category_multimedia TINYINT,
FOREIGN KEY (domain_id) REFERENCES domain (domain_id),
PRIMARY KEY (category_id),
UNIQUE UC_category_id (category_id));
CREATE TABLE location(
domain_id INT NOT NULL,
registrar VARCHAR(50),
main_ip VARCHAR(20),
sec_ip VARCHAR(20),
main_name VARCHAR(60),
sec_name VARCHAR(60),
FOREIGN KEY (domain_id) REFERENCES domain (domain_id));
CREATE TABLE dates(
domain_id INT NOT NULL,
date_registered DATE,
date_expires DATE,
FOREIGN KEY (domain_id) REFERENCES domain (domain_id));
CREATE TABLE techcontact(
domain_id INT NOT NULL,
tech_first VARCHAR(20),
tech_last VARCHAR(20),
tech_address VARCHAR(60),
tech_phone VARCHAR(20),
tech_email VARCHAR(40),
FOREIGN KEY (domain_id) REFERENCES domain (domain_id));
CREATE TABLE admincontact(
domain_id INT NOT NULL,
admin_first VARCHAR(20),
admin_last VARCHAR(20),
admin_address VARCHAR(60),
admin_phone VARCHAR(20),
admin_email VARCHAR(60),
FOREIGN KEY (domain_id) REFERENCES domain (domain_id));
CREATE TABLE access(
domain_id INT NOT NULL,
access_host VARCHAR(80),
access_username VARCHAR(20),
access_password VARCHAR(20),
FOREIGN KEY (domain_id) REFERENCES domain (domain_id));
Note that many tables doesn't have primary key, this is because you
have put relations 1...1 in separate tables, which is correct, but
then is when you have to think about performance. Also, there is not a
single solution to design a database, but in my opinion, I would drop
tables with relation 1...1 and create a single table, then, separate
tables for relations 1...N.
To help you design databases, I recommend you using a tool instead
writting them by hand, the easiest I know is Dezign:
DeZign for Databases V3
http://www.datanamic.com/dezign/index.html
While I prefer the previous version, the last one is very nice.
Regarding your diagram, I think there are some problems with it:
-The category table structure I think it will need to be this way:
Categories
----------
category_ID
domain_ID
category_name
-Also, the relationship is not good, I mean, from domain to
categories, a domain can have 1 to N categories, but from categories
to domain, a category can also hold 1 to N domains
-The notes relationship must be, from domain to notes, 0 ... 1, but
from notes to domain, must be 1 ... 1
-Also, the money, purchased, offers and sold tables and relationships
are not very well designed...
I think it would be better if you post the full exercise and then I
teach you how to design the database and then, create the MySQL
sentences that make it up.
Regarding data normalization, multiplicity and primary keys, well,
it's a very extensive theme, but you should think about it following
these simple rules:
-Rules: To relate two tables, you must specify which kind of relation
have, that is, given 1 record from Table A, how many would I get from
Table B?
0...1 : Zero or one, that is given 1 record from Table A, Table B can
return 0 or just 1 record
0...N : Zero or More, that is given 1 record from Table A, Table B
can return 0 or N records
1...1 : Exactly one, that is given 1 record from Table A, Table B
will return just 1 record
1...N : One or More, that is given 1 record from Table A, Table B
will return 1 or N records
And relationships have two ends, from Table A to Table B and from
Table B to Table A.
This is really huge topic to talk about, I suggest you to take a look
at this nice article:
Normalization Is a Nice Theory
http://www.island-data.com/downloads/papers/normalization.html
And after that, please, just post any question you have as a
clarification, I will answer any of your questions until you
understand these topics.
Regards. |
,
0 Comments
)
