Solved

Composite and/or compound key

Posted on 2000-02-14
10
1,237 Views
Last Modified: 2008-03-10
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
0
Comment
Question by:DWB
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 10

Expert Comment

by:brewdog
ID: 2520568
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
 

Author Comment

by:DWB
ID: 2520616

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
 
LVL 10

Accepted Solution

by:
brewdog earned 50 total points
ID: 2520627
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:DWB
ID: 2520634

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
 

Author Comment

by:DWB
ID: 2520649
I should have but you on my Valentine list.  As always your help is invaluable.  

Cheers
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2520650
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
 

Author Comment

by:DWB
ID: 2520664
Thanks!!

I'll drop you a  line this weekend!
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2520683
OK,
I'm looking forward for your message.
My best wishes,
Dedushka
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2520697
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
 
LVL 10

Expert Comment

by:brewdog
ID: 2522926
Glad I could help out, DWB. :o)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question