Mysql table structure and designing

Question

I have two tables both i am working like below concept...

When any record will be inserted 'id' of first table ('id' is unique and auto_increment), I want to use this id value for further insertion in my second table (that means id will be foreign key).How should I should to design my table?

1. If i am inserting values and further fetching last inserted value to again insert in second table then there will more query execute for a single insertion and this may take more time execution in mysql.

2. If i will make on extra table (counter_id_table) that will keep on id value with auto increment.When any new record will be going to insert first i will fetch value of 'counter_id_table' and this value will be the id of my first table and this value will be foreign id in second table.When i will fetch value from 'counter_id_table' again i will update with after one increment.

How i should to design my table for better execution?

Answer 1

Hey if your relation between the tables is one - to - one, then merge the two tables, else do the following:

In table 1, let it be as it is,

In table 2, Make a column with the name of table 1 and relate id of the first table with that...

Answer 2

The first option is how it's usually done. You can use MySQL's mysql_insert_id() to retrieve the id of the entry you inserted, right after the insert query. Don't worry about speed or load, this happens in a fraction of a second and MySQL can do this in its sleep.

So you'll get something like this:

`user`
- id
- name
- etc

`user_stuff`
- id
- userId // foreign key
- stuff
- etc

$sql = "INSERT INTO user (name) VALUES ('foo')";
mysql_query($sql) or die(mysql_error());

$userId = mysql_insert_id();

$sql = "INSERT INTO user_stuff (userId, stuff) VALUES ($userId, 'stuff')";
mysql_query($sql) or die(mysql_error());