Search code examples
mysqlrelational-databasenormalization

What is the correct mysql (relational) database design structure for

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?

Solution

  • 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).