Questions about Primary Keys, Foreign Keys and Sub-forms

[JohnKlassen]

Hi,

I realize that my questions may be beyond the scope of this forum.

As you know from previous posts, I now know how to add primary keys to a table as I am loading the data using uniqid(). I am now ready to add a foreign key to a table and join them. I need some direction.

I have 2 tables. One lists all of the units in a building. The second table lists which units have violations and up to 3 rows of information per violation. (Each violation can have up to 3 warnings and there can be multiple different violations per unit.) The user would look at a browse screen of units, choose a unit which would open the sub-form, showing all of the violations for that unit. The user could then update a violation or add another warning or even a new violation.

Both tables have a primary key that was created when loading the data. I assume the violations table would also have a foreign key pointing back to the units table.

How does the foreign key get populated in the violations table?

Since the primary key and the foreign key do not include the unit number, how do the rows get linked?

I have read many of the posts on foreign keys and looked at wiki and videos and am still confused. I understand bits and pieces but not how they all come together. In addition to this web site, are there other places I should look at to get a better feel for how it all fits together?

Thanks,

John

[admin]

John,

Are you importing violations records and units records?

If so how do you know which violations go with which units?

Steven

[JohnKlassen]

Steven,

Initially I am importing data for both tables. After that the user would add/update the violations. The unit table would not change very much after the load. If it does, the user would add or update the data.

Both the unit table and the violations table have the unit number.

John

[admin]

John,

I would suggest adding a field to violations called vio_unit_id and filling it with the unit's id that it matches with.

By creating a Procedure Activity to populate the new field.

like..

Code: Select all

   $t = nuRunQuery("SELECT * FROM unit");

   while($r = db_fetch_object($t)){

      nuRunQuery("UPDATE violations SET vio_unit_id = '$r->unit_id' WHERE unit_no = '$r->unit_no'");

   }

Steven

[JohnKlassen]

Steven,

Thanks,

John

[admin]

.