0

I have a table in my database called Resource. This has common information about a resource (a resource could be an article, a static web link, a YouTube video, or any number of other types).

For my example:

Table: resource

primary key - id,
column - type,
column - title,
column - description,
column - created_on,
column - updated_on

Table: resource_video

primary key - id,
column - youtube_id (spose this could be the primary key, but not relevant in the question).
column - ...

Table: resource_weblink

primary key - id,
column - url
column - ...

So essentially the resource table contains the generic (pertains to all resources), columns, and then tables are set up to hold resource_type specific data. What is the best (normalized) way to create relationships between resource and resource_type.

My first instinct is that it should be a one to one identifying relationship between the two tables with a foreign key of resource_id in the resource_video and resource_weblink tables, or would there be a better way to handle this situation?

Community
  • 1
  • 1
Aaron Murray
  • 1,920
  • 3
  • 22
  • 38

1 Answers1

1

I'd make the primary key of each resource_* table have a foreign key constraint to the id column of resource. There's no need for a separate id for each resource subtype. See this thread for an example of how to do this (look at the SupportSystem hierarchy in the accepted answer).

Community
  • 1
  • 1
Ted Hopp
  • 232,168
  • 48
  • 399
  • 521
  • So essentially I was on the right path with a slightly different approach of using the id in the resource table as the primary key in the resource_* table. A side note, I could eliminate the resource.type column altogether as it would be irrelevant as well. I think that was where I was confusing myself. Correct path, with a few unnecessary columns. Thank you! – Aaron Murray Oct 02 '11 at 17:42
  • No, you can't eliminate resource.type. It's highly relevant for data integrity. Look at how the "pub_type" column works in this SO answer: http://stackoverflow.com/questions/4969133/database-design-problem/4970646#4970646 – Mike Sherrill 'Cat Recall' Oct 17 '11 at 14:25