media9 — 2013-11-27T14:22:46-05:00 — #1
I am making a mailing list with PHP and MySQL. I have two questions. Here's the table I have normalized:
Question 1 - How can I enter values in 4 different tables (Client, Product, Department and Area) using a single HTML input form? For example, when I enter data for my client "Andrew" into the client table, related information of Product, Department and Area table should be populated as well through a single SQL INSERT query ? How to achieve that?
Question 2 - Is my normalization ok? I am asking because I have made composite primary key in Product, Department and Area tables, using same foreign key (email). Is this correct?
mittineague — 2013-11-27T16:53:49-05:00 — #2
To do INSERT in more than one table in one query MySQL lets you create a TRIGGER
Sorry I can't help with normalization, I'm still fairly new to it myself, though if you search for posts made by @r937; you should find something helpful.
felgall — 2013-11-27T20:33:25-05:00 — #3
You could just run the inserts one after another after the form is submitted.
If you want to ensure that database integrity is maintained then set up the multiple inserts as a transaction so that you can COMMIT if they all run successfully and ROLLBACK if any of them fail for any reason.
r937 — 2013-11-27T22:18:58-05:00 — #4
with 4 INSERTs... use START TRANSACTION and COMMIT as stephen suggested
yup, composite PKs are fine
vpalukuru9 — 2013-11-28T03:25:23-05:00 — #5
Instead of using the email as your foreign key why dont you generate a sequence number in client table and use that column in the child tables. This way you can save the storage.
media9 — 2013-11-30T05:31:06-05:00 — #6
Thank you so much everyone for your insights. I appreciate this forum. Got a lot of information from you guys.