[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Composite and/or compound key

Posted on 2000-02-14
10
Medium Priority
?
1,254 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 200 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

650 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