splitting data into multiple tables

Question

I am building a employees page.

Some of the information goes into an 'employees' table but some of it goes into a 'availability' table that is referenced to the 'employee' table:

availability: id / employeeid (unique id from employees table) / monday available / and on and on /

So I don't have that unique ID from the employees table until I create them.

Is it fine to do a query where I set the employee info and then a query to get the last created row in the employee table and then use the unique id from that to set the availability...

Or is that messy and should I have a create employee page and THEN a set availability page?

So basically I want to know if it is cleaner and 'better' coding to separate the two functions?

Answer 1

Of course create employee first, availability then.

If your tables are InnoDB, you can do it in a transaction so that you can rollback the whole update if something goes wrong.

Answer 2

Is it fine to do a query where I set the employee info and then a query to get the last created row in the employee table and then use the unique id from that to set the availability...

Yes, that sounds OK. If you use an autoincrement column for employeeid, you can then use mysql_insert_id() or equivalent to retrieve that last inserted id safely. Don't do SELECT MAX(employeeid) FROM ...., because you might get problems when loads of people are using it concurrently.

Answer 3

You can easily get the last insered record via

mysql_insert_id()

After that, you can insert an availability record for the desired employee. Note: I would choose a framework that takes care of these issues, like Symfony or Cake.

Answer 4

Using the "last created row" may not always work the way that you're expecting and may complicate things in the future if there's growth or if another programmer assumes the project. If I understand what you're looking for, you should instead have 3 tables. One table for employees, one table for availability, and a third table should be used to store unique records for the association. In the association table each row will have columns for : a unique ID, the employee id, the availability id.

Answer 5

Adding to @Quassnoi's answer:

You would add the employee record, then use the MySQL LAST_INSERT_ID() function to find the autoincremented unique id for the employee record you added. You can then feed that value back into the availability INSERT statement.

More details are on the MySQL manual page at http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

It's important that you not use a SELECT statement (e.g. SELECT MAX(employee.id)) since there might be other uses adding records as well. LAST_INSERT_ID() is specific to your connection