Relational Databases

[kenwvs]

I have a designed a form that people will fill out to generate a work order for a piece of equipment when it fails.

I have created relational (I think) databases, where the tables are all normalized, but am not sure how to tie it all together now.

As an example I have the following tables, with their purpose:

workorder - majority of the data goes in this table
parts - the parts number and parts descriptions go in this table
employees - this table contains the employees data and the employee who generated the workorder go in here

workorder_parts - to tie the workorder and parts tables together
workorder_employee - to tie the workorder and employee tables together

My host does not offer innodb table type so I need to do this manually

When I create the query to INSERT the data to the DB table, am I using INSERT INTO [color=red]TABLE[/color]    where [color=red]TABLE is the workorder, parts or employees tables, or is it the workorder_parts, etc. table.[/color]

I am thinking it is the main (parent) tables, but I don't understand how I will tie them together.  As an example, if I use the parts table, how do I tie it together so that the parts number/description in the parts table are referenced to the workorder number they were generated from?

Here is the code I am using:

[code]if (!isset($_POST['Submit']))
{
else
{
$work = $_POST["work"];
$sched = $_POST["sched"];
$name = $_POST["tech"];
$site = $_POST["site"];
$serial = $_POST["serial"];
$hours = $_POST["hours"];
$starts = $_POST["starts"];
$issue = $_POST["issue"];
$severity = $_POST["severe"];
$resolution = $_POST["resolve"];
$assistance = $_POST["assist"];
$safety = $_POST["safe"];
    $number = $_POST["number"];
    $description = $_POST["description"];

{
if (!empty($_POST['number']))
  foreach($_POST['description'] as $key=>$description) {
  $description_esc = mysql_real_escape_string($description);
  $number = intval($_POST['number'][$key]);
  $query = "INSERT INTO parts (description,number) VALUES ("
    . "'{$description_esc}', "
    . "{$number} )";
mysql_query($query) or die(mysql_error().$query);
}

mysql_query ("Insert into `workorder`(work, sched, site, serial, hours,
starts, issue, severity, resolution, assistance, safety) VALUES
('$work', '$sched', '$site', '$serial', '$hours', '$starts',
'$issue', '$severity', '$resolution', '$assistance', '$safety')")or die(mysql_error());

mysql_query ("Insert into `employees` (name) VALUES ('$name')")or die(mysql_error());
[/code]

[drranch]

you would setup the following in your database...

One to many relationship -

Employee would be one and the workorder would be many.
So each employe can have many workorder records associated with them.

Table structure
make sure that your workorder table has a field for the employee name and set it as the 'key' for that table.  This will prevent more than one employee getting added to this table.

I don't think you want to do the one to many relationships with the parts/workorder section as many parts can be associated with many workorders and vice versa.

What program are you using to upload your tables with? I use Plesk and the 'key' terminology is 'unique'.

[fenway]

As for relating the records, simply pull back LAST_INSERT_ID().

[obsidian]

[quote author=fenway link=topic=107201.msg429836#msg429836 date=1157633058]
As for relating the records, simply pull back LAST_INSERT_ID().
[/quote]

or, you could use the PHP function mysql_insert_id(). same result