2

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:

  1. 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
  2. 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
  3. 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
  4. 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.

2
  • What database are you using?
    – Erik
    Mar 31, 2017 at 15:11
  • @Erik - It should work with different database engines, so it shouldn't be based on some engine specifics.
    – Murval
    Apr 3, 2017 at 15:08

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.