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

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.

  • 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... – CallMePhilip Oct 25 '14 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. – user40980 Oct 25 '14 at 18:29
  • here's what I got... graphicsdesigned.co.uk/MuseumSchema.jpg – CallMePhilip Oct 25 '14 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." – Robert Harvey Oct 25 '14 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. – Robert Harvey Oct 25 '14 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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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