how should I make this database?

[CyberShot]

I have been searching for days for a good tutorial that covers this and have not been able to find one. I am using wamp and have phpmyadmin set up and everything is working fine. I am trying to figure out how to make my database work properly. Lets say I have three tables

 

names

phone

bills

 

I want the phone and bills table to be linked to the names table so that when I select a name, the phone and bills information will come up with the name. but if no phone number is entered and no bills are entered, then nothing should come up with that. The only way I can find to do it is to manually update the foreign key field when you insert records. I don't want to do that, I want the database to auto increment. I can't figure out how to do it. Do you know of a tutorial?

[ignace]

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

[CyberShot]

that had some good information in it but still misses the mark. there was nothing good in there about how to set up the database in phpmyadmin or in sql. It leaves out the foreign key inforamation. Doesn't talk about how the tables are linked together when you have null values. The search continues

[ignace]

there was nothing good in there about how to set up the database

 

You sure you read the article?

[CyberShot]

yep. I am sure. Yes, it talked about 1nf and second and so on but didn't show how to relate the tables. It talked about how you would get an error if you tried to add to the tables in a certain way. I have gotten those errors. What I wanted the article to do was give a step by step on how to set up the database it was talking about. Primary and foreign keys. and then show how to insert data into those tables while keeping the relationship. It did not do that.

[ignace]

Declaring a foreign key on a MyISAM table won't do much anyway. Have you decided on an engine that fits your project? If your project is all about reading and barely writing then MyISAM will be a good fit and foreign keys are out of the picture.

[CyberShot]

i used the nnodb or whatever it is. Here is exactly what I am trying to figure out

 

I want to make a database for a bill application. So I will have

 

tables = names, phone, amounts

 

so phone and amounts will be linked to names by foreign keys. so the idea is that I can enter 4 names, 2 numbers and one amount and be able to pull the information out of the database that goes with the right name. That means that I think the phone table and the amounts table needs to know how to keep track of what information goes with which name. I have tried setting the relationships but I get errors when it comes time to input the data. So how do you set up the database so that it will keep track of the id's automatically like the auto increment? Or do I have to tell each table to automatically insert blanks if there is no data so that each insert will match up?

 

[ignace]

DROP TABLE IF EXISTS bill;
DROP TABLE IF EXISTS contact;
DROP TABLE IF EXISTS user;

CREATE TABLE user (
  user_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ..other
) ENGINE=InnoDB;

CREATE TABLE contact (
  contact_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  ..other
  INDEX (user_id),
  FOREIGN KEY (user_id) REFERENCES user (user_id),
  PRIMARY KEY (contact_id)
) ENGINE=InnoDB;

CREATE TABLE bill (
  bill_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  ..other
  INDEX (user_id),
  FOREIGN KEY (user_id) REFERENCES user (user_id),
  PRIMARY KEY (bill_id)
) ENGINE=InnoDB;

[CyberShot]

I am sure I have done this. I will try it again and let you know how it goes. Usually I get an error saying that i can't update or insert to the child table due to a foreign key restraint.

[jdavidbakr]

The foreign keys do not auto-increment.  You have to enter the data into the first table, and then get the ID (that was created via autoincrement) using mysql_insert_id().  Then use that to insert into the table that contains the foreign key.

 

So, in your case, you would insert into the user table and use mysql_insert_id() to get the user_id you just created.  Then you'll use that to set the value of user_id in the insert statement to the contact and/or bill tables.  If the user already exists, you look up that user and get the user_id, and again use that to insert into the other tables.  The foreign key relations don't do that for you (the engine would have no way of knowing what record you want to link); the relations merely prevent you from having an invalid link and other automated management once the link exists.

[CyberShot]

okay, that makes sense. Now do you know of a tutorial that shows this? after 3 days of searching, I have been unable to find one. Someone could make this tutorial and sell it and make money. There are website that buy them. Like this one for example

 

http://marketplace.tutsplus.com/