Solved

Composite and/or compound key

Posted on 2000-02-14
10
1,245 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

705 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