I'm new in programming. I'm designing a database that has two tables: Addresses and Customers. I don't know how to assign foreign keys. I don't know which way is better to use:
Method1:
Customer Table Columns:
CustomerID FirstName LastName Address ID
Addresses Table Columns:
AddressId Country State City AddressLine
Method2
Customer Table:
CustomerId FirstName LastName
Addresses Table:
AddressId CustomerId Country City AddressLine
In other words I don't know where to place the foreign key.
If each customer can have many addresses, then you want to use method 2 because then there can be multiple records in Addresses for each customer in Customer.
However, it seems to me like you might need a many-to-many relationship. What happens if two customers share the same address?
You can accomplish this by including a third table, e.g. CustomerAddresses that logically sits in between Customer and Addresses. This third table will hold the foreign keys from both of the other tables -- CustomerID and AddressID.
In your first method, one customer could only be associated to one address, as it has only one id referring to an address. But nothing prevents several customers from having the same address id. So this represent a many-customer-to-one-address relation. That's not what you want.
In your second method, the relation is reversed. Each address is associated to a customer, several addresses can be associated to the same customer, the is a one-customer-to-many-addresses relation.
In sql, to retrieve the addresses associated with a customer, you would do:
select * from address where customer_id = '<some customer_id>'
If you are trying to implement one to many, so the recommended way is to put the primary key for the table besides the one into the table of the many as a foreign key..
For example: if one costumer have many addresses and address have one costumer. so you will have to put the costumerId as a foreign key in the table of the address.
if one address can have many costumers and one costumer have one address. so you will have to put the AddressNumber as a foreign key in the table of the costumers.
Good Luck
I will point out that you may actually have a many to many relationship. One address can have more than one customer and one customer can have more than one address. In this case you use a Join table to model the relationship that contains the addressid and the customerid and nothing else. This would have a join PK of both those fields to ensure uniqueness.
