Foreign Key column issue

Question

I am trying to connect two tables using a foreign key.

Table 1: (Plaque):

PLAQUE_ID   PLAQUE_TYPE      facility_id    FACILITY    PLAQUE_SUB_LOCATION
2468        Lettering         26          Gorab Building    DONOR WALL FRIEND LEVEL
2469        Lettering         36          abc Hospital  MUSIC & ENTERTAINMENT WALL
2476        Facility Plaque   46          abc Hospital  1ST FL OFFICE ROOM

Table 2: Facility

FACILITY_ID FACILITY             FACILITY_DESC  PLAQUE_ID
26          Gorab Building                         2468
36          abc Hospital                           2469
46          abc Hospital                           2476

I don't want the facility table to have same rows as Plaque table. I want distinct names in facility column. I thought the only way to do that is make FACILITY column as foreign key to Plaque table but I am confused because about that can we make such a column a foreign key. I have seen only ID columns as foreign keys. The reason behind distinct values in FACILITY column is because every row will have a large description about that FACILITY in the FACILITY_DESC column.

An answer would be very appreciated.

Answer 1

You don't need the PLAUQUE_ID in the Facility table because you have a facility_id in the Plaque table and that is enough to make the connection between the Plague and the Facitlity tables. Just use a simple join. For example:

SELECT Plaque_ID, Plaque_Type, Facility FROM Plaque INNER JOIN Facility ON Plaque.Facility_ID = Facility.Facility_ID 

I thik you should set the Facility column of the Facility table to be unique and also remove the Facility column of the Plaque table. Remember the Plaque table should describe the plaque properties onlu. Also consider adding a new PlaqueType table and replacing the Plaque Type column of the Plaque id with its coresponding foreign key ot the PlaqueType table.

Here is a simple explanation of how to join tables http://www.w3schools.com/sql/sql_join_inner.asp