Clever Geek Handbook

Splitting data into multiple tables

I am creating a staff page.

Some information goes into the "employee" table, but some of it goes into the "availability" table, which refers to the "employee" table:

availability: id / employeeid (unique id from employee table) / monday available / and enabled and enabled /

So I don't have this unique id from the employee table until I create them.

Is it okay to run a query when I set the employee information and then query to get the last created row in the employee table and then use a unique id to set accessibility ...

Or is it messy and I should have an employee create page and THEN an accessibility accessibility page?

So, basically I want to know if this is a cleaner and "better" coding to separate the two functions?

+2


source to share


5 answers


Adding to @Quassnoi's answer:

You would add an employee record and then use a MySQL function LAST_INSERT_ID()

to find the auto-incrementing unique ID for the record employee

you added. Then you can return that value to the statement availability

INSERT

.



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

It is important that you do not use a SELECT statement (for example SELECT MAX(employee.id)

), as there may be other uses that add records. LAST_INSERT_ID()

refers to your connection

+3


source


Of course, first create employee

, availability

.



If your tables are there InnoDB

, you can do this in a transaction so you can undo the entire update if something goes wrong.

+1


source


Is it okay to run a query where I set the employee information and then the query to get the last created row into the employee table and then use a unique id to set the presence ...

Yes, that sounds fine. If you are using an autoincrement column for employeeid, you can use mysql_insert_id () or equivalent to safely restore that last inserted ID. Don't do SELECT MAX(employeeid) FROM ....

it because you might run into problems when many people use it at the same time.

+1


source


You can get the latest recorded recording easily 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.

+1


source


Using "last line created" may not always work as you expect and may complicate things in the future if growth or if another programmer envisions a project. If I understand what you are looking for, you should have 3 tables instead. To store unique records for an association, use one table for employees, one table for accessibility, and a third table. In the association table, each row will have columns for: unique id, employee id, availability id.

+1


source






More articles:


All Articles