Google Answers Logo
View Question
 
Q: MySQL Multi-table Database Design Help - EASY! ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: MySQL Multi-table Database Design Help - EASY!
Category: Computers > Programming
Asked by: thanksmate-ga
List Price: $100.00
Posted: 20 Feb 2004 04:53 PST
Expires: 21 Mar 2004 04:53 PST
Question ID: 308770
Hello,

So my problem is that my instructor for a Db Design/Management course
I'm doing via a correspondence course bascially said my Db for my
final project is sh!t, and that I need multiple tables and
relationships; currently the Db is just one big table, no
relationships (see end).

Well I already started to draw a Third Normal Form in PhotoShop (I
don't know a better way) representing the new multi-tabled database
with relationships that I think will work (see URL one below), but
this is the first time I've done this and I'm stuck and confused on
how I can get it to work and on what the "primary keys", notations etc
should be.
Can you please check out the diagram and help me complete it and
explain to me the "how and why" - I really want to learn! Then I can
code a site, the forms etc.

Here is the preliminary Normal Form of the new Db I need help with:
http://www.gnaunited.com/modules/My_Uploads/user_folders/thanksmate/domain-normal-form.gif
(URL One)
The diagram's original PSD file:
http://www.gnaunited.com/modules/My_Uploads/user_folders/thanksmate/domain-normal-form.psd
(1.8 MB)

To Clarify: I want to make the database below multi-tabled and with
relationships and require help designing that.
The attributes are fine, I'm just confused on how to connect tables
together and draw a Normal Form - I think my main problems are that I
don't completely understand what "data normalization", "multiplicity"
and "primary keys" are, especially primary keys.

Thanks in advance!

The original Db design that supposedly sucks:
CREATE TABLE `domains` (
`id` int(11) NOT NULL auto_increment,
`domain_name` varchar(50) default NULL,
`domain_type` varchar(20) default NULL,
`admin_first` varchar(25) default NULL,
`admin_last` varchar(25) default NULL,
`admin_address` varchar(60) default NULL,
`admin_phone` varchar(40) default NULL,
`admin_email` varchar(40) default NULL,
`tech_first` varchar(25) default NULL,
`tech_last` varchar(25) default NULL,
`tech_address` varchar(60) default NULL,
`tech_phone` varchar(40) default NULL,
`tech_email` varchar(40) default NULL,
`date_reg` date default NULL,
`date_exp` date default NULL,
`registrar` varchar(50) default NULL,
`main_ip` varchar(60) default NULL,
`sec_ip` varchar(60) default NULL,
`main_name` varchar(60) default NULL,
`sec_name` varchar(60) default NULL,
`host` varchar(60) default NULL,
`user` varchar(35) default NULL,
`pass` varchar(35) default NULL,
`date_pur` date default NULL,
`price_pur` decimal(9,2) default NULL,
`date_sold` date default NULL,
`price_sold` decimal(9,2) default NULL,
`sold_who` varchar(60) default NULL,
`date_offers` date default NULL,
`price_offers` decimal(9,2) default NULL,
`offers_who` varchar(60) default NULL,
`notes` text,
`category` varchar(30) default NULL,
PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=6 ;

NOTE:
The database is to manage a domain name portfolio
If I feel that you have taught me a lot I will make a generous
donation - up to $100
** This question must be answered within 3 days of its posting or it's void **
Answer  
Subject: Re: MySQL Multi-table Database Design Help - EASY!
Answered By: joseleon-ga on 20 Feb 2004 06:55 PST
Rated:5 out of 5 stars
 
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.

Request for Answer Clarification by thanksmate-ga on 21 Feb 2004 07:23 PST
Thank you for your quick response and software recommendation, which I
installed and used, but for the moment I prefer PhotoShop for my
diagrams. The article, "Normalization Is a Nice Theory" was also very
helpful.

Simply put, my project is to make a business task easier by means of a
database. I chose to develop a Domain Name Portfolio Db for a small
company that is a domain name broker.

Now I need to create the Normalized Data Model and develop the forms,
reports and queries, which I've half done; I already did the forms,
reports and queries for the one-tabled database, which did not meet
the project's requirements as I was required to develop a multi-tabled
database...
So I'm having a lot of difficulty now understanding how to add data
via a form and to retreive it from the multi-tabled Db.

Can you please use my project as an example and show me the correct
way to connect to the Db and provide the SQL file, one form, a report
and one query as an example so I can see how it is done and then I
must do the rest myself. I will then release your $100 and include the
full ($100) bonus. Okay?
The example form must add data to different tables. Then I would like
a page that displays the data (report) and one page with a query that
will only display a selection of the data.
Please use MySQL, HTML/PHP and then zip it up and upload it so I can
download it to see how it is done.
If you're not keen to provide an example, I will consider my question
already answered and will release your $100 immediately.

ThanksMate

Clarification of Answer by joseleon-ga on 23 Feb 2004 06:42 PST
Hello, thanksmate:

Here is the sample you need:
 
http://www.xpde.com/samples.zip
 
It contains the SQL sentence to create the database and the sample.php
script that shows you how to insert data in several tables and link
them together in a report. Bear in mind that I have made the source
code as much educational as possible, is far from being professionally
coded.
 
Feel free to request for more information.
 
Regards.

Clarification of Answer by joseleon-ga on 25 Feb 2004 03:34 PST
Hello, thanksmate:
  Have you had any problems with the source code I sent you? It's everything ok?

Regards.

Clarification of Answer by joseleon-ga on 27 Feb 2004 01:14 PST
Hello, thanksmate:
  Did my examples were useful to you?

Regards.

Request for Answer Clarification by thanksmate-ga on 27 Feb 2004 15:41 PST
I haven't had time to look at the sample, but I will, and I will leave
you a rating now and the bonus as promised...
Maybe you're interested to help me verify another question:
http://answers.google.com/answers/threadview?id=311567
Thanks

Clarification of Answer by joseleon-ga on 28 Feb 2004 04:42 PST
Hello, thanksmate:
  Thanks for the tip, I'm going to take a look to the other question.

Regards.
thanksmate-ga rated this answer:5 out of 5 stars and gave an additional tip of: $100.00
Very Good... provided answer with extra information and provided an
example that helped me understand the subject even better... Thank
You!

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy