Mysql table structure and designing
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?
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.
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?
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());
精彩评论