Composite and/or compound key

I'm doing an assignment on relational data analysis, and I have a problem with normalisation of data ,I'm familiar with Primary keys and
Foreign keys, but in my book it mentions "composite or compound key" any idea what this is ?
 
Any help greatly appreciated
DWBAsked:
Who is Participating?
 
brewdogConnect With a Mentor Commented:
Not quite. Primary Keys *always* refer to a single table. You can't have a primary key spanning more than one table. More than one *field* in a table, not more than one table.

Here is another example: an employee database.

I want to keep track of all of my employees, so I have an Employment table. My initial thought would be to make Social Security Number my primary key, but then I have a decision to make: what if an employee leaves and returns? Do I update their HireDate, or do I want to keep a history of their comings and goings? If I want the history, then Social Security Number and HireDate would be a great combination primary key. I highlight both fields (using my Ctrl key and clicking on each row selector) and then click the primary key button on the toolbar.

You're second thought is right on track, though. You can't have nulls in primary key fields, so if you expect some entries to be null, you might want to consider breaking that field (with some other related fields, most likely) out into another table. And as you say, there is a balance: having 25 two-field tables just to keep yourself from storing nulls is not going to help, especially if you ever have to write queries. On the other hand, having all 50 fields in one table may not be helpful either.

Be logical, and always consider what the database will be used for and by whom. Sometimes it doesn't matter enough to break the data out, and sometimes it's crucial.

brewdog
0
 
brewdogCommented:
Sure. You do not have to select just one field as the primary key in your tables. For instance, in a fast food restaurant, you might have an orders table that looks like:

OrderNumber     OrderItemNumber       ProductNumber       Price
1                               1                             27                    .99
1                               2                             39                   2.47

and so on. You need the OrderNumber field so that you know to group a number of items on an order, but that can't be your primary key by itself. So, in this case, you could combine OrderNumber and OrderItemNumber and have your unique identifier. (OrderItemNumber would be the sequential number of the item being ordered by one customer.)

Does that make sense?

brewdog
0
 
DWBAuthor Commented:

I believe I understand:

Simplistic terms, Simple Keys for a single table; Compound/Composite Keys span more than one table?

Now: A balance between the number of tables and the storage efficiency is called for here, correct?
Either way, this will cause a secondary key to exist. Decisions - put up with nulls in order that a small number of tables is obtained OR increase the number of tables with no nulls? True/False.

I’m I on the right track?
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
DWBAuthor Commented:

I believe I understand:

Simplistic terms, Simple Keys for a single table; Compound/Composite Keys span more than one table?

Now: A balance between the number of tables and the storage efficiency is called for here, correct?
Either way, this will cause a secondary key to exist. Decisions - put up with nulls in order that a small number of tables is obtained OR increase the number of tables with no nulls? True/False.

I’m I on the right track?
0
 
DWBAuthor Commented:
I should have but you on my Valentine list.  As always your help is invaluable.  

Cheers
0
 
DedushkaCommented:
Hi,
"composite or compound key" - combination of fields that are unique identifier. There are several different keys may be determined for one table, they can be single keys or composite keys, one of them we usually choose as "primary" key. Other keys are defined as unique indexes. To define composite key as primary key you should highlight all fields you need and click "key" button on toolbar. On the left of each field in composite key you'll see "key" icon.

:-)
Dedushka
0
 
DWBAuthor Commented:
Thanks!!

I'll drop you a  line this weekend!
0
 
DedushkaCommented:
OK,
I'm looking forward for your message.
My best wishes,
Dedushka
0
 
DedushkaCommented:
By the way, in theory there is no difference between keys, but in practice, "primary" key determines sort order for storing table. When you open table in datasheet view, you'll see data sorted according "primary" key.
0
 
brewdogCommented:
Glad I could help out, DWB. :o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.