Confused about Primary Keys, and Foreign Keys

Question

Im brand new to database design and, I'm slightly confused about Primary Keys, and Foreign Keys and my database design.

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 tables and IF I've got my keys in the right order.

This is what I've got...

I'm not sure, is this overly complicated? OR would it be easier, to add the contents of the "Location" and "Admission" table into the "Details" table and just relate the "Details" to "Categories" table. So, from my understanding going from 1-to-many (at present?) to then becoming 1-to-1???

—

I apologise if this makes no sense, I am new, obviously VERY confused. It's tricky teaching yourself!

Thanks everyone, I'm really grateful. If you need to see a larger image, it's here...

www.graphicsdesigned.co.uk/Museumscema3.jpg

Cheers!

Answer 1

Teaching yourself database design is indeed very tricky. You might benefit from finding a good tutorial that would help you master the basics. If you want suggestions concerning a good learning vehicle, ask.

Here is a very oversimplified summary of some of the basics.

Before you can design a database, you have to analyze the subject matter in some detail. To quote GregH, "completely understanding how the business works". One way to do this is to get some reliable source on Museums and how they work in the real world, or whatever approximation to the real world you are willing to settle for. You may rely on your own memory for this, but it's better to get some input from experts on Museums, your subject matter. ER modeling can help summarize what you learn.

Another thing you need to spend a little time on is how you intend to use the data. Your design can be influenced by your intended use. This can turn out to be a moving target. You may decide to use the data in more than one way, somewhere in the future. Think ahead as best you can.

Then, you are ready to design your tables. Normalisation is one fairly mechanical approach to avoiding certain pitfalls that can creep up on you if you just design at random. You table design should reflect the relational model of data. The relational model is slightly different from the ER model, in that foreign keys are used to link tables together. But the question of which table non key data should go in is nearly impossible to get right unless you have a clear understanding of the subject matter.

good luck.

Answer 2

Having had a look at your rethink with just three tables (www.graphicsdesigned.co.uk/MuseumSchema4.gif) it still needs more tweaking. What if a museum is linked to more than one category? The way you have it set up at the moment you'll be adding another row to the museums table for each category that the museum is in. What you need is a "link table" which just contains three fields: MuseumCategoryLinkID (primary key), MuseumDetails_ID and Categories_ID. With this new table you can take the categories_id_fk field out of the museumdetails table.

Link tables are good for "many to many" relationships like this (each museum could be in many categories, each category could match many museums).

You might also need a separate calendar table because the way you have it at the moment you're assuming that every Monday is the same opening times, every Tuesday is the same etc. Large museums are probably open every day except Christmas, so you might be able to get away with this, but small museums might close on bank holidays (not a very good business practice but your database still needs to be able to deal with it).

With the location table, are you going to list the bus numbers as a comma separated list in one field (i.e. "23,49,146") or were you thinking of listing them individually? If you wanted to list them individually then you'd have to split the route numbers into a table of their own and then have another link table joining the location with the routes (bearing in mind that one museum is on several routes and one bus can pass several museums).

Although the location table does list location-related information, some of it could be moved back into the museums table because each museum only has one location (i.e. you're not going to get three museums at the same latitude/longitude, but you might get three all at the same tube stop). Some people will say that lat/long is a property of the location rather than of the museum but it's borderline, there's no right/wrong answer with something like that.

If this is just an academic exercise to teach yourself database design, have a think about how you might extend the design to store more information such as a count of visitors per day, or a list of exhibits with start/end dates and so on.

By the way, your initial design is actually very close to what a data warehouse design would be, with the main table (known as a fact table in data warehouse jargon) just containing a list of foreign keys to supporting tables (knows as dimension tables).

Answer 3

Let's assume that you would want to have a "Detail" entity. This would assume that these detail tuples are unique and that a given detail tuple may apply to more than one museum. Given this, you have the "MuseumDetails" entity correct. The primary key for MuseumDetails would either be the concatenation of the details_id (the primary key of the "Details" table) and the museum_id (the primary key of the "Museum" table, or it would be a surrogate key that you would generate...something like Museum_Detail_ID.

Now, your Museum table should not have the detail foreign key (details_id) since the relationship between "details" and "museums" is established in the MuseumDetails intersection entity. This is not only redundant in the Museum entity but you risk data integrity problems if you implement it this way. In fact it looks like you have this problem with all of your other entities. You are carrying the foreign key through the intersection entity into the Museum entity.

One thing I think is strange (which goes back to the whole thing of having a "details" entity) is that you have the "museum name" in details. The name is an attribute of a Museum so the name should really show up in the Museum entity.

The categories and admission entities are designed in a very limiting manner such that you must add attributes every time you want to add new information. This should not be the case. For example, the Categories entity could have the attributes "Category_ID" and "category_name". The intersection entity (MuseumCategories) would have the "Museum_ID" and the "Category_ID" as foreign keys. By simply including a record in the intersection table (MuseumCategories) you are indicating the Museum has a section for that category. By it not containing are record, you know a given Museum does not have a section for that category.

You shouldn't do your "Admission" entity and relationship the way it is done either. While technically, you can do it this way, again, you would have to add a new column for every new type of admission. This could better be done by have an admission entity with an admission_id, admission_name (values may be: Free, Adult, Child, etc), price, etc. Then you would have an intersection table (say Museum_Admission) that contained the intersection between the Museum and Intersection table. This is where it gets into completely understanding how the business works and how it needs to be modeled. There is more but my response is getting lengthy.