locked
Foreign Keys RRS feed

  • Question

  • I have created a 2 tables and was wondering if I could have 2 foreign keys in one table?

    Trying to enter grocery receipts and then get a report for grocery shopping list.

    Any sample databases already created with the same objective?
    Tuesday, October 6, 2015 8:29 PM

Answers

  • You can have more than one FK in a table, but you don't have to in your case. What you need to do is split the Aisle field out of the category table. The category table should have just the category data. You need an Aisle table.

    An item has a category and an aisle (unless an item has more than one place in the store). So what you need is a location table and an aisle table. It would have an item id and an aisle. The item id would be the FK to the item table. The item in the item table has a category ID as a FK.

    Capeesh?


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Tuesday, October 6, 2015 9:18 PM

All replies

  • You can have more than one FK in a table, but you don't have to in your case. What you need to do is split the Aisle field out of the category table. The category table should have just the category data. You need an Aisle table.

    An item has a category and an aisle (unless an item has more than one place in the store). So what you need is a location table and an aisle table. It would have an item id and an aisle. The item id would be the FK to the item table. The item in the item table has a category ID as a FK.

    Capeesh?


    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals


    Tuesday, October 6, 2015 9:18 PM
  • Thanks for your prompt response.

    Regards.

    Wednesday, October 7, 2015 7:18 PM
  • It cannot be assumed that stores never move the location of categories, i.e. that the aisle is time-independently determined by the key of categories.  Also a category could occupy more than one aisle in a store simultaneously

    The way to model this would, in broad outline, be to have the following tables:

    Aisles
    ....Aisle  (PK)

    Categories
    ....Category (PK)

    CategoryAisles
    ....Category  (FK)
    ....Aisle  (FK)

    The primary key of the above is a composite one of both columns.

    Items
    ....ItemID  (PK)
    ....Item
    ....Category  (FK)
    ....Aisle  (FK)

    In this table Category and Aisle are not, as might be thought, a composite foreign key referencing the composite primary key of Category Aisles (so no such relationship should be created), but are independent foreign keys referencing the primary keys of Categories and Aisles respectively.  This reflects the fact that aisle is not time-independently determined by category.

    For data entry into items via a bound form, correlated combo boxes could be used, allowing a category to be selected first, and then an aisle from a list (often a list of one) of those aisles in which the selected category is located, e.g. its RowSource would be along these lines:

    SELECT Aisle
    FROM CategoryAisles
    WHERE Category = Form!cboCategory
    ORDER BY Aisle;

    Note that natural keys have been used for Aisles and Categories, not surrogate autonumber keys.  This makes the correlation of the combo boxes much easier in a continuous form as, if a surrogate key is used, the aisle combo box would appear blank in the case of previous records where the category was not at the time in one of the current aisles for that category, as represented by rows in CategoryAisles.  No data would be lost, but would be invisible to the user, and 'hybrid' controls would be necessary (see ComboDemo.zip in my public databases folder at https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169 for examples of the use of hybrid controls where a natural key is not possible).

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, October 7, 2015 10:20 PM Typo corrected
    Wednesday, October 7, 2015 10:19 PM