0

I cant work out how you get multiple addresses to one customer, for example. Any ideas on how to implement this?

Thanks

Matt
  • 22,721
  • 17
  • 71
  • 112
user1876281
  • 21
  • 1
  • 3
  • 1
    One table for customers, with a primary key. A second table for the addresses, with a foreign key to the first table. *[Even if these terms don't mean much to you, google them and learning how to use them will form a fundamental basis of your relational data knowledge.]* – MatBailie Dec 04 '12 at 21:16
  • I did do that, but it doesnt seem right, im not sure if it can be identified that way – user1876281 Dec 04 '12 at 21:33
  • Give example data that you think can't be modelled that way. We can't mind read ;) with the two table system many addresses can refer to the same customer => Each customer has many addresses. – MatBailie Dec 04 '12 at 22:43

1 Answers1

2

Use a foreign key in the addresses table

CREATE TABLE Customers
(
C_Id int NOT NULL,
other_stuff whatever,
PRIMARY KEY (C_Id),
)

CREATE TABLE Addresses
(
 A_ID int NOT NULL,
 C_ID int NOT NULL,
 other_stuff whatever,
 FOREIGN KEY (C_Id) REFERENCES Customers(C_Id),
 PRIMARY KEY (A_ID)
)

This can be used to implement a one-to-many relationship.

For example if you have George the Customer with C_ID=55 then if you wanted to give George a new address then you would insert an entry into the addresses table with the value of 5 as C_ID. 

insert into addresses (C_ID,blah,blah) values (55,blah,blah)

and then if you wanted to get all of George's addresses you would say:

select * from addresses where C_ID=55

If that didn't make sense, this here has pictures http://net.tutsplus.com/tutorials/databases/sql-for-beginners-part-3-database-relationships/

Sheena
  • 15,590
  • 14
  • 75
  • 113