5

Context:
I'm working on a database of museums and Categories will be one way the user will be able to search. e.g click on "Music" and a list of music related museums turn up. Pretty simple.

Problem:
I'm stuck and confused on the Normalisation process of the "Categories" table...

categories_PK IF is the entry point to the ID rows table, how do I reference the actual categories themselves? Such as "Architecture" and "Artists Architects and Writers Homes" in one query? I'm confused — I don't see how the values will be referenced?

As one museum may fall into more than one category, the only other idea I had was to ad a Foreign Key to every category such as Archaeology_PK? Here's what I've got....

enter image description here

Improve this question

2 Answers 2

Reset to default
8

You need two tables:

Categories MuseumCategories

They will look like this:

Categories CategoryID (Primary Key) CategoryName MuseumCategories MuseumID (Foreign Key) CategoryID (Foreign Key)

The MuseumCategories table connects each specified category to a museum. Each record in the MuseumCategories table connects one category to one museum. You can have many such records for the same museum.

Improve this answer
13
  • Brilliant, that's really helpful, thanks so much. Does that mean I have to add additional table for every other table I already have too? Such as "DETAILS" "CATEGORIES" "ADMISSION" "LOCATION"? I've got an image I can show... Commented Oct 25, 2014 at 18:24
  • 1
    @CallMePhilip this structure is a many to many relationship mediated by the table. If other tables also have a many to many relationship, they may need such a structure too. If a single 'Details' is shared by multiple other objects and those objects may have multiple details, then yes, it may too need a many to many structure. Commented Oct 25, 2014 at 18:29
  • here's what I got... graphicsdesigned.co.uk/MuseumSchema.jpg Commented Oct 25, 2014 at 18:31
  • 4
    Many to many relationships shouldn't be avoided if that's what you are modeling. There's no other way to represent them in a relational database. You only need one table for bridging "categories." Commented Oct 25, 2014 at 18:50
  • 1
    Your original problem description requires a many-to-many relationship. If you were only tagging museums with a single Category, then you could get away with a one-to-many and avoid the linking table. Commented Oct 25, 2014 at 19:59
1

This is where you use a bridge table, a table that exists to link your two tables.

So, you have a table MuseumCategory which has at least two columns, Museuem and Category. Both being foreign keys to their respective tables.

You might want to have other columns, such active or start and end dates, depending upon your system you might want auditing information (created by, date created, approvedby, whatever).

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.