CODEWITHSUNDEEP
sqloracle-sqldeveloper
axel_p
axel_p

Reputation: 51

Tables & PK /FK for relational database

I need to design a relational database to answer queries such as fetching order details for a particular customer ,find customer details for a particular order and to retrieve addresses of customers .

this is the dataset

This is what i think i need to do but im not really sure

Customers (Cust_no (PK), Order_no*, Cust_name, Phone, Address, City, State, Postcode, Country)

Orders (Order_no(PK), Cust_no*, Product_no, Quantity , Unit_price, sales_rep)

Addresses (Cust_no * (PK) , Postcode (PK) ,Address, City, State, , Country)

I will be creating tables and inserting data into the database which im hoping shouldn't be a problem but first i need to be clear on the relations along with PK and FK

Upvotes: 1

Views: 762

Answers (2)

Jonathan Wood
Jonathan Wood

Reputation: 67193

I would remove the Order_no from the Customer table. Using this FK, a customer can only reference one order. Instead, use the FK in the Orders table. That way, any number of orders can refer to a particular customer.

Similarly, the Addresses table should have probably have a FK to your customers, and no address column in your customers table. (Personally, I usually prefer to just add the address columns directly into the Customers table.)

Also, why do you have individual address columns (city, state, etc.) in both the Customers and Addresses table? And why are you using the postal code as the FK? That means you can't have more than one customer with the same postal code.

Here's how I'd approach it (assuming a separate table for addresses):

Customers: Id (PK), Name, Phone
Orders: Id (PK), CustomerId (FK), ProductId, Quantity, UnitPrice, SalesRep
Addresses: Id (PK), CustomerId (FK), Address, City, State, Postcode, Country

Upvotes: 2

Alex M
Alex M

Reputation: 147

Almost. Your Orders have to have Customer_ID, Address_ID. Customers don't have to have OrderID FK.Customer > Orders 1> Many.

I wouldn't include Postal code in primary key, as you may have different addresses in the same postal code for the same customer. You can create just an index for Postal Code to speed up search.

Upvotes: 0

Related Questions