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
Solved

Composite and/or compound key

Posted on 2000-02-14
10
1,230 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

840 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