Confused about Primary Keys, and Foreign Keys

database-designdatabase-recommendation

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…

enter image description here

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!

Best Answer

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.

Related Solutions

Sql-server – SQL Server : primary keys advice to the whitepaper needed

Your points are unrelated to database design: choice of natural or surrogate key is an implementation decisions after conceptual and logical models are complete

In addition to comments and other answers:

some natural keys work well such as currency or language codes (CHF, GBP, DE, EN etc)
avoiding composite keys forces you to always join intermediate tables (rather than simple) parent-grandchild
adding a surrogate key in unnecessary for link tables

Edit: example of "composite keys"

Assume: t1 has child t2 has child t3

If you had the key of t1 in t3 (composite key) you can join t1 and t3 directly.
t1 key is also the left hand column of t3 key so you don't need an extra index
With a surrogate key/FK, you have to join via t2
You need extra indexes on the FK columns in t2 and t3 which

This latter option with the "always use surrogate key" dogma

adds complexity
decreased or reverses disk space "savings"

Sql-server – SQL Server Primary key / clustered index design decision

You are correct to separate "clustered index" from "primary key":

A clustered index is the organisation of data on disk is better if
- narrow
- numeric
- increasing (strictly monotonic)
The primary key identifies a row

Note: GUIDs make poor clustering keys

In this case, with the surrogate column, the table has 2 candidate keys:

ProductHistoryID
ProductNo + CreatedDateTime

Assumed convention states that the ProductHistoryID becomes the PK, but you can leave the PK on (ProductNo, CreatedDateTime): it will just be non-clustered. Which leads to indexes:

clustered index should be on ProductHistoryID
unique non-clustered index on (ProductNo, CreatedDateTime)

Example

CREATE TABLE Product (
    ProductHistoryID int NOT NULL IDENTITY (1,1) NOT NULL,
    ProductNo ...
    CreatedDateTime ...

then you a choice of

    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductHistoryID)
    CONSTRAINT UQ_Product UNIQUE NONCLUSTERED (ProductHistoryID)

    CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductNo, CreatedDateTime)
    CONSTRAINT PK_Product UNIQUE CLUSTERED (ProductHistoryID)

Also, the pattern you have is a "type 2 Slowly Changing Dimension"

Best Answer

Related Solutions

Sql-server – SQL Server : primary keys advice to the whitepaper needed

Sql-server – SQL Server Primary key / clustered index design decision

Related Question