Avatar of royjayd
royjayd asked on

Is primary key mandatory?

hi guys
I have a table 'supplier' with these 4 columns

supplier_id   product_id  supplier_name   cost
1                      100                   IIT                        $200
2                      100                   RAM                   $199
2                      200                  RAM                   $500
3                      500                   GAC                   $1499

I have multiple suppliers selling products but 2 different suppliers
can sell the same product,  example i have supplier_id 1 and
supplier_id 2 selling the product with product_id 100 (for a different cost)

so in this scenario what could be my primary key and what should be the
foreign key?

Do i relly need a primary key in the first place? or can i just use composite keys in this scenario?

thanks
J
DB2

Avatar of undefined
Last Comment
royjayd

8/22/2022 - Mon
cyberkiwi

Best practice is that you have an integer primary key that means nothing, which
makes it easier to create foreign keys
better for sequential storage
less space when many indexes are involved

But for simple cases, yes, a composite key can be used if required.
SOLUTION
cyberkiwi

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
momi_sabag

in your case, the primary key should be supplier_id + product_id
you don't have to create a primary key
you might as well just define an index on those 2 columns to enforce uniqueness

in a similar way, supplier_id should be a foreign key to the suppliers table and product_id should be a foreign key to the products table,
but then again, this is not mandatory
ASKER
royjayd

momi
that is what i had in my mind aswell, primary key can be supplier_id + prod_id
so is this called a composite key ?

thanks everyone for answers.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
royjayd

yeah i agree, my design is flawed. What i had in mind was how do i reduce the number of tables by having only two tables PRODUCT table and SUPPLIER table and have the product cost in SUPPLIER table which actually doesnt make much sense because cost is related to product and should not be in the SUPPLIER table. So it looks like i have to create an intersection table SUPPLIER_PRODUCT.
However is there anyway i can work with two tables PRODUCT and SUPPLIER with the cost in the PRODUCT table?

cyberkiwi

>> that is what i had in my mind aswell, primary key can be supplier_id + prod_id
>> However is there anyway i can work with two tables PRODUCT and SUPPLIER with the cost in the PRODUCT table?

Be that as it may, you are still better off creating a unique constraint on (supplier_id, prod_id).
And a primary key on just something sequential, a simple number (id)
For the reasons stated somewhere above.

One day, when you are game, split out supplier_id and supplier_name to a new table, where you can start storing supplier specific details like address, phone and create other tables that link off it.  Supplier_id remains in supplier_product as a foreign key to supplier table.
Theo Kouwenhoven

2 methods to find it your self:

1. Just remove the columns from the design, that are suerely NOT a key....
2. a.Take the most logical field.
   b. Add the field(s) that are necesary to make the record unique.

so:
supplier_id +  product_id

Just try to learn sonething about "Normalisation" (Codd's idea)

Just start here : http://en.wikipedia.org/wiki/Database_normalization
and dig as deep as you like :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
royjayd

I am following this structure which experts suggested above

Supplier table (contain only supplier info)
Product table (contain only product info)
supplier_product (contain info which are relevant to supplier and product like cost and productdealer)

It looks pretty good for now. Can anyone sumup all the normalization rules for future reference.
thanks every one.
Theo Kouwenhoven

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:
Eliminate duplicative columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).
Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:
Meet all the requirements of the first normal form.
Remove subsets of data that apply to multiple rows of a table and place them in separate tables.
Create relationships between these new tables and their predecessors through the use of foreign keys.
Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:
Meet all the requirements of the second normal form.
Remove columns that are not dependent upon the primary key.
Fourth Normal Form (4NF)

Finally, fourth normal form (4NF) has one additional requirement:
Meet all the requirements of the third normal form.
A relation is in 4NF if it has no multi-valued dependencies.
Remember, these normalization guidelines are cumulative. For a database to be in 2NF, it must first fulfill all the criteria of a 1NF database.

checkout this:
http://en.wikipedia.org/wiki/First_normal_form
http://en.wikipedia.org/wiki/Second_normal_form
http://en.wikipedia.org/wiki/Third_normal_form
http://en.wikipedia.org/wiki/Fourth_normal_form
http://en.wikipedia.org/wiki/Fifth_normal_form






SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
royjayd

lowfatspread...all valid points

>>what do you mean by cost?
Its the cost of a product FROM a specific supplier. So cost by itself would not make sense.

>>>do the suppliers not offer discounts for volume ordering
supplier wil offer discounts, so how should my table design change?

>>>what about other attributes for the product... colour?  
i am recognizing each product with its own unique label.

>>>is this for a real world scenario
yeah, it is still in initial stages for a amazon.com like website.

so far  i have this
Product table
product_id   pk
prod_name
prod_label
manufac_year

supplier table
supp_id    pk
supp_name
supp_city
supp_street
supp_state
supp_zip

Supplier_Product

supp_id  (PK)
pro_id (PK)
pro_cost
pro_expert
pro_expert_phone

I dont have any foreign key, would that be ok?
which columns do i index?

thanks everyone for helping out.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
royjayd

we are still in the usecase gathering phase. i am going to have atleast two more entities: Order and customer.  
Order entity will have table(s) related to Oder placed like order id
 and customer (individual or corporate) entity will have table(s) related to Customer.
ASKER
royjayd

oops missed out points for momi. can the admin reopen the question so i can reassign points .thanks