Simple resources database

Question

I have a small database with several hundred resources of varying types (medical, education and research, for example). Each resource will need to be identified by its region. Some of the resources serve multiple regions. I need to be able to define each resource by its type, and it's region.

Since one region will have many resources, and one resource can serve many counties I figure I should have a junction table between them, right?

My question is, should I have a junction / linking table for each resource type? Should I have a table of education_resources, regions and link them with a junction table education_regions? And do the same thing for the rest of the categories?

Answer 1

Keep columns common to all resources in the Resource table. Sub-type tables have only columns specific to each one.

Answer 2

Creating tables with names like education_resources and medical_resources may make it difficult for you to extend your database if and when you add additional resource types. Instead you could have tables as:

Resource_Type (resource_type_id, resource_type_name)
Resource_Region (resource_region_id, resource_region_name)
Resource_counties (resource_county_id, resource_county_name)
Resources (resource_id, resource_type_id,...)
Resource_Region_Map (resource_region_id, resource_id)
Resource_County_Map (resource_county_id, resource_id)

Will this work for you, or will it make things more complicated?