Design Problem - SQL Server Forums

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.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Design Problem

Author

Topic

drew22299
Starting Member

26 Posts

Posted - 2007-02-08 : 09:29:44

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,
AddressLine1
AddressLine2)

www.myspace.com/drew22299

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-08 : 09:37:34

See here: [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78800[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rlaubert
Yak Posting Veteran

96 Posts

Posted - 2007-02-28 : 16:38:00

Create table customer
(Customerid int not null primary key,
Firstname varchar(20),
SurName varchar(20))

Create table orders
(OrderId INT not null PRIMARY KEY,
ProductID Int,
qty decimal,
Customerid int)

Create table Product
(ProductId INT not null PRIMARY KEY,
Price decimal,
Product varchar(20),
Description(20))

Create Table Address
(
CustomerId INT not null PRIMARY KEY,
AddressLine1,
AddressLine2,
zipcode int)

Create table zipcode
(zipcode int,
city varchar(30),
State varchar(30),
Country varchar(30)
)

--set up references between
Customer.customerid and address.customerid,
orders.customerid and customer.customerid,
orders.productid and products.productid,
address.zipcode and zipcode.zipcode

queries then would be something like
select firstname, surname, orderid, product, description, qty, price, (qty*price) as total
from customer c inner join orders o on c.customerid = o.customerid
inner join address a on c.customerid = a.customerid
inner join products p on o.productid = p.productid

Raymond Laubert
MCDBA, MCITP:Administration, MCT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources