Secondary Key

[jk]

I am in the begining stages of designing a DB which would track the expenses
of 40 customers . Each customer has a unique five digit ID and the expenses
would go against their location cost center number which is a unique six
digit number. The customers are billed monthly and each invoice can only have
one expense account billed for the services provided(One to One
Relationship). In the customer table, thier unique ID would be the primary
key and i would like to know if the cost center would be a secondary primary
key since each customer can only have one cost center?

CUS TABLE INVOICE TABLE EXPENSE ACCTS
CUS ID=22525---PK MKZ25260----PK 47472-----PK ( AUTONUMBER)
COSTCTR=242456---PK

 

[tina]

comments inline.

I am in the begining stages of designing a DB which would track the expenses
of 40 customers . Each customer has a unique five digit ID and the expenses
would go against their location cost center number which is a unique six
digit number. The customers are billed monthly and each invoice can only have
one expense account billed for the services provided(One to One
Relationship).

each invoice can only be billed to one expense account, okay. but can each
expense account only have one invoice billed to it, *ever*? probably not.
remember that in determining relationships, you have to define *each side*
of the relationship. my guess is that: one expense account may have many
invoices billed to it, but each invoice is billed to only one expense
account. that's a one-to-many relationship, not one-to-one.

In the customer table, thier unique ID would be the primary
key and i would like to know if the cost center would be a secondary primary
key since each customer can only have one cost center?

there's no such thing as a "secondary" primary key. each table may have only
one primary key; though a primary key may consist of multiple fields if
necessary, it is still one key. a table may have more than one field with a
unique index, or more than one group of fields that combine to form a unique
index, though.

CUS TABLE INVOICE TABLE EXPENSE ACCTS
CUS ID=22525---PK MKZ25260----PK 47472-----PK ( AUTONUMBER)
COSTCTR=242456---PK

i don't know what a cost center is, in your business model, but is it a true
statement that one cost center may have more than one customer assigned to
it? does each customer use only one expense account, *ever*? or different
expense accounts? recommend you do some more reading on relational design
principles, and also read up on the role of the primary key in a table. here
are a couple links that may help you:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101
http://www.dbpd.com/vault/9805xtra.htm

hth

 

[jk]

Hi Tina,

I am new to this and thanks for the insight and the links. You are correct
that i did not think it through from both sides of the relationships and the
cost center(Account) is meant to identify the "many" customers expenses
against this account. Should this be in a separate table?
Thanks

 

[tina]

your business entities aren't clear to me, and i hesitate to make structural
recommendations with much less than a full picture of the process being
modeled. i will recommend that you go ahead and study relational design as i
mentioned before; then create tables/relationships based on your process
analysis. at that point you might want to post your structure, with
explanations of the entities, and ask for feedback. it's easier for us to
review your design and make suggestions when we're all speaking the same
relational language, so you can understand the context of our questions and
comments.

hth

 

[Pat Hartman \(MVP\)]

Your design allows for only one cost center per customer so there is no need
to add a separate unique index or to change your primary key to a compound
primary key. The only way you could use a unique index on cost center is if
cost center is unique across customer records.