Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
drew22299
Starting Member
26 Posts |
 Posted - 2007-02-08 : 09:26:15
|
| Hello,I'm new to using SQL and I'm trying to figure out how to design tables and their relationships. Is the following correct? If not, how should it be? All comments and adivce welcome. Thanks,Create Table Customer(CustomerId INT not null PRIMARY KEY,FirstName varchar(20),SurName varchar(20),AddressId Integer references Address(AddressId))Create Table Orders(OrderId INT not null PRIMARY KEY,Product varchar(20),Description varchar(20),Price decimal,ProductId Integer references Product(ProductId),AddressId Integer references Address(AddressId))Create Table Product(ProductId INT not null PRIMARY KEY,Price decimal,Product varchar(20),Description(20),Create Table Address(AddressId INT not null PRIMARY KEY,AddressLine1AddressLine2)www.myspace.com/drew22299 |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-02-08 : 09:36:32
|
quote:
Create Table Customer(CustomerId INT not null PRIMARY KEY,FirstName varchar(20),SurName varchar(20),AddressId Integer references Address(AddressId))Create Table Orders(OrderId INT not null PRIMARY KEY,Product varchar(20),Description varchar(20),Price decimal,ProductId Integer references Product(ProductId),AddressId Integer references Address(AddressId))Create Table Product(ProductId INT not null PRIMARY KEY,Price decimal,Product varchar(20),Description(20),Create Table Address(AddressId INT not null PRIMARY KEY,AddressLine1AddressLine2)
1. Where is CustomerID in orders table2. Can price in order can be different than price in Product table?3. Separate table for Address may not be necessaryHarsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
drew22299
Starting Member
26 Posts |
Posted - 2007-02-08 : 10:27:13
|
Thanks,  I want to be able to store all order details in the order table so you can see who has oredered what. Instead of just having two tables (customer and order).Also, is this the right way to get all customers who have ordered a product?select CustomerDetails.Name, OrdersDetails.Description, OrdersDetails.Price, OrdersDetails.OrderIdFrom CustomerDetails, OrdersDetailsRIGHT JOIN ProductOn CustomerDetails.CustomerId = OrdersDetails.CustomerId and OrdersDetail.ProductId = Product.ProductIdI'm abit confused with this. I can use queries like the above one on existing tables and get the results, but when it comes to creating tables I don't know what needs to be a foreign key and why! www.myspace.com/drew22299 |
|
|
|
|
|
|
|
|
Design Question