How to check if table exists in MySql db?

MySQL Multi-table Database Design Help - EASY!

• 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:

  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.