CODEWITHSUNDEEP
database
user3165708
user3165708

Reputation: 21

Microsoft Access 2013 - Multiple Foreign Keys in the same Record

I’ve encountered a problem with pulling multiple foreign keys into one record in a Microsoft Access table.

I’ve got two tables with these fields:

Table 1: Owners Fields: Owner ID (Primary Key) First Name Last Name

Table 2: Ships Fields: Ship ID (Primary Key) Ship Name

I need to create a relationship between Table 1 and Table 2 which shows who owns the ship. I’ve tried adding a number field to Table 2 called Owner ID (Foreign Key) and this works absolutely fine.

Working Example:

Table 1 – Owners                  Table 2 – Ships 
Owner ID (Primary Key)__          Ship ID (Primary Key)
First Name              \         Ship Name
Last Name                \________Owner ID (Foreign Key)

Unfortunately my ships in Table 2 can have multiple owners (up to 5) at the same time. My problem arises when I try to create a series of linking fields in Table 2.

Not Working:

Table 1 – Owners                     Table 2 – Ships 
Owner ID (Primary Key)__             Ship ID (Primary Key)
First Name              \            Ship Name
Last Name                \           Owner1 ID (Foreign Key)
                          \______/   Owner2 ID (Foreign Key)
                                 \   Owner3 ID (Foreign Key)

Can anyone recommend any workarounds so I can show multiple owners taken from the Owners table in the Ships table?

Thanks for your help!

Upvotes: 2

Views: 6136

Answers (2)

simon at rcl
simon at rcl

Reputation: 7344

The problem is that it looks like Access doesn't allow Nullable FK's and so all Owner fields would have to be filled in, no matter how many owners there are.

The only solution to this I can think of is to introduce a ShipOwner table, which has ShipID and OwnerID columns (as FK's to the Ship and Owner tables). You can then have as many Owners as you like. 

Pros: You can add things like %Owned if that matters
Cons: The software has to enforce the limit of 5 owners

Biggest Pro: it will work!

Cheers -

EDIT: The first para is wrong: Access does let you add nullable FK's. However I still thing the suggestion here is a good one. Repeating Groups (Owner 1 to 5) is against Normalisation rules, and this suggestion is normalised.

Upvotes: 0

iDevlop
iDevlop

Reputation: 25262

Your database design is definitely incorrect.

In the case you explain, you have a many-to-many relationship between Ships and Owners, which MUST translate into a "relationship table" in the relational model.
In this case: a [Ownership] table, with 2 fields, being the 2 Primary Keys (PK) of the related tables.

In other words, you need 3 tables:

  • Ships(ShipId, ShipName, Whatever) PK is ShipId
  • Owner(OwnerId, FirstName, LastName) PK is OwnerId
  • OwnerShip(ShipId, OwnerId) PK is made of the 2 FKs

Upvotes: 2

Related Questions