nuBuilder Forum

Posted: **Thu Jan 03, 2013 5:05 am**

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

Posted: **Thu Jan 03, 2013 10:54 pm**

John,

Are you importing violations records and units records?

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

Steven

Posted: **Thu Jan 03, 2013 11:17 pm**

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

Posted: **Thu Jan 03, 2013 11:52 pm**

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

Posted: **Fri Jan 04, 2013 5:06 am**

Steven,

Thanks,

John

Posted: **Mon Jan 07, 2013 3:38 am**

nuBuilder Forum

Questions about Primary Keys, Foreign Keys and Sub-forms

Questions about Primary Keys, Foreign Keys and Sub-forms

Re: Questions about Primary Keys, Foreign Keys and Sub-forms

Re: Questions about Primary Keys, Foreign Keys and Sub-forms

Re: Questions about Primary Keys, Foreign Keys and Sub-forms

Re: Questions about Primary Keys, Foreign Keys and Sub-forms

Re: Questions about Primary Keys, Foreign Keys and Sub-forms