Database Design – Handling Multiple Foreign Keys on a Single Column

foreign keynormalization

I have a table Item with many items of many types. Differently typed items are linked to different resources each of whom have a table with primary key.

Currently to achieve this I have two columns in Item table: Resource type and Resource ID.

The question is, what would be propper way to refactor Item table in a way so I can set up foreign keys to Resource ID?

Preferably without creating many new tables just for this reason or without adding many new columns to Item table, where most of them would be empty.

**EDIT**

Additional note: aside from resource id's, all items also have multiple other fields, some of which may be used only by some Resource type.

As far as I have looked it up, I am seeing 4 options:

Have a table Item with columns Resource type and Resource ID as well as all additional columns (case explained above).
- (+) smaller amount of unused cells
- (-) some unused cells
- (-) no foreign keys on Resource ID
Have a table Item with additional column for each Resource type as well as all additional common columns.
- (+) availability of foreign keys on Resource ID
- (-) a lot of unused cells
Have a table Item with only common columns (or even with only Item ID) + additional table with columns: ID, Item ID, Value type, Value BIGINT, Value INT, Value STRING. Second table will be used for Resource ID's as well as fields that are unique for one or another Resource type.
- (+) smaller and constant amount of unused cells
- (+/-) more space per unique field, but overall smaller than in other cases.
- (-) no foreign keys on Resource ID
New table for each Resource type
- (+) availability of foreign keys on Resource ID
- (+) more control over each Resource type
- (-) a lot of new tables, number of which will increase as new Resource type's will be introduced.

I am now evaluating, which of these cases will be more beneficial to my case.

Best Answer

It was decided to go with something between second and third options - adding additional table with columns Value type and separate columns for each Resource type.

(+) easier to manage in code
(+) availability of Foreign Keys on Resource ID
(+) no overhead on table count
(+) ability to add multiple different Resource type's to each Item
(-) uses extra space for each unused Resource type for each field
(-) lacks ability to cascade delete related Item, when resource with Resource ID is deleted.

Related Solutions

Sql-server – Performance effect of indexing foreign keys

It may still be useful to have indexes on the larger table to help the optimizer for specific SELECT queries, even if you will "never" change any of the values.

However, without a lot more knowledge of your system and the types of queries you will run, it's pretty difficult for any of us to able to tell you whether you should have indexes there or not. I'm afraid it's just something you're going to have to test.

Some background in this great article by Erin Stellato:

http://www.sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

You also might consider avoiding the joins in some cases - when you are sure the values will never change, and eliminating the join can simplify the execution plan, you can consider just inline constants, e.g.

SELECT CASE RatingID
  WHEN 1 THEN 'poor'
  WHEN 2 THEN 'average'
  ... 
END
FROM dbo.BiggerTable
...

Also I suggest never naming something ID. If it's a RatingID, call it RatingID everywhere it exists in the model.

MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram

To make first,middle,last name searchable, should I make them Primary Keys?

No, any field in any table is "searchable".

A primary key is a unique identifier of a row in a table. Your idPeople field satisfies this requirement, though since you stated the table will have millions of rows, you'll want to make that an INT or BIGINT. The maximum value of SMALLINT is 65535. See http://dev.mysql.com/doc/refman/5.0/en/integer-types.html

How would I allow to search through ItemCode.codeSection and return all "People" that match?

One way would be:

SELECT * 
FROM PEOPLE
WHERE idPeople in 
    (
      SELECT idPeople FROM Item WHERE idItem in 
      (
          SELECT idItemCode from ItemCode WHERE codeSection = 'Some Section'
      )
    )

Another way would be to use JOIN.

I hope I understand the foreign key concept. It's on the "Item" table so it allows multiple items per "People". Is this the correct way?

Looks correct to me. The notation is called crows foot notation, and on the Item side (1 line and a "crows foot", it's saying 1 to many items. On the People side (parallel lines), it's saying 1 and only 1 person. Using this notation, some of your other relationships appear incorrect. (People to County, in particular)

There will be separate lists for different counties, should I create a View for this?

You could, but they could also just be separate SELECT statements or Stored Procedures.

Best Answer

Related Solutions

Sql-server – Performance effect of indexing foreign keys

MySQL Database Normalization Foreign Keys. One to Many relationship. With Diagram

Related Question