Database Help

Hi,

I have a question about setting up a couple of database tables.

I'll try to explain using an example:

We have a customer (stored in a customer table)

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Who was sent an invoice for an order he placed. That invoice was sent to his current address at the time (123 Cherry Lane).

Let’s say he moved to a new address. We would update his customer record accordingly.

CustomerCode: JDOE (Primary Key)

CustomerName: John Doe

Address: 987 Apple Road

City: Dubbo

State: NSW

PostalCode: 2830

Now, let’s say that we want to be able to look back at JDOE’s invoices and see where they were shipped too. This means we have to store that address somewhere.

Now if we said just store it on the invoice table like:

InvoiceNo: 0001 (Primary Key)

CustomerCode: JDOE (Foreign Key)

Address: 123 Cherry Lane

City: Dubbo

State: NSW

PostalCode: 2830

Amount: $300.00

That would break 3^rd normal form as the address doesn’t depend on the primary key (InvoiceNo [0001]).

We want to be able to see the address from the invoice table via a relationship/lookup/computational column/whatever.

How would we go about doing this in SQL Server version 8?

Cheers,

Pete

I think it is pretty common to have a set up like that. So you might have have a table(s) that lists default values for customers and an actual invoice table that includes actual ship address. You can divide it up for storage and performance reasons as necessary but most companies need to have that historical info available.

My two cents :-)

|||

Hi,

You can have an additional column say address2 for the current address. I know this sounds naive, but sometimes this is the best way to get things done. Now you may ask, what if I have more than 2 addresses, then boy ur going to have a tough time coming up with a schema, which stores historical information.

Good Luck and let me know, how u went about it.

PP

|||

G'day,

Not be blunt, but the above suggested example is very poor design. One particular (and there are several) solutions to this is to first separate the address details from the customer details:

Customer Table

CustomerCode, CustomerName,

Address Table

CustomerCode, AddressID, Addresstype, line1, Line2, PostCode,State

This will then allow a customer to first of all have more then 1 address recorded (physical, postal, work, home etc) as well as almost conforming to 3NF (the State column would in fact need to become a "StateID" column lookup into a "State' table...).

Or, if you really didn't want to store multiple addresses, just keep your same design and create a trigger on the customer table to record any address details that change into a seperate table (example only):

CREATE TRIGGER CustomerAddress_upd

on CustomerTable AFTER UPDATE

AS

BEGIN

IF ( UPDATE(Line1) OR UPDATE(PostCode) ) --list all required cols here

BEGIN

INSERT INTO AddressAudit (CustomerCode, Line1, PostCode, DateChanged)

SELECT CustomerCode, Line1, PostCode, getDate()

FROM CustomerTable c

JOIN Inserted i ON c.CustomerCode = i.CustomerCode

END

END

Then, if required, just query the audit table.

Cheers,

Rob

sql

database design??

Hello,

I am designing my first database with 5 tables for a demo project and am not sure if it works. an example below.

2 of the many things I want visitors to the site to do is find a company by the industry sector they belong to,..and

what sort of service or products they can supply. For instance a Employment agency maybe under professional services

Table 1 Customer

Customer_ID = primary key,,,, Sector_ID = Foreign key

Comapany Name, Address, Phone, Postcode etc

Tabel 2 Industry Sectors

Sector_ID = primary key,,,,Customer_ID= foreign key

banking, Education,Prof Services, etc

Table 3 Trading Activity

Trading_ID = primary key,,,,Sector_ID = Foreign key, Products_ID= Fk

Employment Agent, School, Lawyer etc

Table 4 Products

Products_ID = primary key,,,,Trading_ID = foreign key

Supply frozen foods, transport services, sports goods, etc

Table 5 Account

Account_ID = primary key,,,,Customer_ID = foreign key

Account Name, Credit Limit, Payment Terms, Open date, Account contact etc

One big point of confusion is, can I have the Customer_ID from the principal Customers table

in every table as a foreign key or must the tables be chained together one after the other as such.

Advice appreciated

Thanks

Hi

There are some problems in your design.First thers are some general rules you should keep in mind.

For 2 objects A B, if there are 1-many relation between A and B, primary key of A should be inculded as foreign key in B .

if there are many-1 relation between A and B, primary key of B should be inculded as foreign key in A .

if there are many-many relation between A and B, you need to create a new table A-B,primary key of B and A should be inculded in the new table

if there are 1-1 relation between A and B, Columns of A and B could be included in a single table .

For table1 and table2 if one customer belongs to many industry sectors and one industry sectors have many customers ,then you should create a new table with primary key of talbe1 and table2 inculded. Table 3 and talbe 4 are the same.

If I misunderstand your meaning ,pls tell me .

Database design problem

I'm trying to built a database for ecommerce and I'm stuck with this problem.

I'll use a grocery store for the example.

First here's the tables.

table product_prd
id_prd
name_prd
description_prd

A product may have different "sizes" like a bag of chips. The table product_prd is linked (one-to-many) to product_size_pds

table product_size_pds
id_pds
idprd_pds //Foreign Key - product_prd.id_prd
name_pds //this contains the name of the size (ex. small,medium, large)
price_pds
weight_pds
lenght_pds
height_pds

I thinks there's a problem with my structure if the product as only one size like a camping chair.

I just can't leave the name_pds field empty or repeat the name from name_prd. It seems bad practice.

Is there a structure that could hold single size product and multiple size products?

Hope this makes sense.

Thanks

Quote:

Originally Posted by ZoeNet

I'm trying to built a database for ecommerce and I'm stuck with this problem.

I'll use a grocery store for the example.

First here's the tables.

table product_prd
id_prd
name_prd
description_prd

A product may have different "sizes" like a bag of chips. The table product_prd is linked (one-to-many) to product_size_pds

table product_size_pds
id_pds
idprd_pds //Foreign Key - product_prd.id_prd
name_pds //this contains the name of the size (ex. small,medium, large)
price_pds
weight_pds
lenght_pds
height_pds

I thinks there's a problem with my structure if the product as only one size like a camping chair.

I just can't leave the name_pds field empty or repeat the name from name_prd. It seems bad practice.

Is there a structure that could hold single size product and multiple size products?

Hope this makes sense.

Thanks

What you might need in your design is transform the product_size_pds table into a mapping table and define the product sizes in a separate table named size:

table product
id_prd //Primary Key (Identity)
name_prd
price_prd
description_prd

table map_product2size
id_prd2size //Primary Key (Identity)
id_prd //Foreign Key from product_prd table
id_size //Foreign Key from size table

table size
id_size //Primary Key (Identity)
name_size //this contains the name of the size (ex. small,medium, large)
weight_size
lenght_size
height_size|||

Quote:

Originally Posted by davef

What you might need in your design is transform the product_size_pds table into a mapping table and define the product sizes in a separate table named size:

table product
id_prd //Primary Key (Identity)
name_prd
price_prd
description_prd

table map_product2size
id_prd2size //Primary Key (Identity)
id_prd //Foreign Key from product_prd table
id_size //Foreign Key from size table

table size
id_size //Primary Key (Identity)
name_size //this contains the name of the size (ex. small,medium, large)
weight_size
lenght_size
height_size

If I take a single size product (the camping chair) with your structure, I still need to enter a size to the product witch I don't think is applicable for a single size product. And for the price it should be in the table size because the price varies with the different size of the product.|||

Quote:

Originally Posted by ZoeNet

If I take a single size product (the camping chair) with your structure, I still need to enter a size to the product witch I don't think is applicable for a single size product. And for the price it should be in the table size because the price varies with the different size of the product.

If you define a size, can it be applied to different products? If yes, then you move the price column to the mapping table such that the product price is defined by the combination of product id and size id. And it's fine to have a product with a single size - it just happens to be a one-to-one relationship inside the mapping table.|||

Quote:

Originally Posted by davef

If you define a size, can it be applied to different products? If yes, then you move the price column to the mapping table such that the product price is defined by the combination of product id and size id. And it's fine to have a product with a single size - it just happens to be a one-to-one relationship inside the mapping table.

I'll try that thanks

Database Design - How do I model a product availability matrix?

I have a list of products with dependencies that determine what a customer
can purchase.
Using a simple example, let's say I have 2 types of widgets, a standard and
a deluxe.
If a customer who has not previously purchased any widget logs on to the web
site, he should see both the standard and deluxe widgets available for sale.
If a customer has previously purchased a standard widget, he should see only
the deluxe widget for sale. If the customer has purchased a deluxe widget,
he should not see any widget products for sale.
Right now this logic is contained in a stored proc but I think it should be
stored in a table that would look something like a matrix.
Has anyone worked with this issue before? If so, how did you handle it?You haven't provided any DDL. I'm guessing but would the following be what
you need:
select
*
from
Widgets w
where
w.Priority >
(
select
max (w2.Priority)
from
Orders o
join
Widgets w2 on w2.WidgetID = o.WidgetID
and o.CustomerID = @.CustomerID
)
I'm assuming that the priority of a deluxe widget is greater than a
standard.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dave" <dave@.nospam.ru> wrote in message
news:%23GC9eQKZFHA.612@.TK2MSFTNGP12.phx.gbl...
I have a list of products with dependencies that determine what a customer
can purchase.
Using a simple example, let's say I have 2 types of widgets, a standard and
a deluxe.
If a customer who has not previously purchased any widget logs on to the web
site, he should see both the standard and deluxe widgets available for sale.
If a customer has previously purchased a standard widget, he should see only
the deluxe widget for sale. If the customer has purchased a deluxe widget,
he should not see any widget products for sale.
Right now this logic is contained in a stored proc but I think it should be
stored in a table that would look something like a matrix.
Has anyone worked with this issue before? If so, how did you handle it?|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
You will need a customer history table and an inventory table at least.
Then I woudl build a VIEW that shows what has not been bought. This
is a declarative/relational approach, as opposed to thinking in terms
of procedures and arrays. Very roughly liek this:
SELECT *
FROM Inventory AS I
WHERE sku
NOT IN ( SELECT sku FROM CustomerHistory AS H);|||Thanks Tom.
I should have been more specific in my question. Right now I am still in
the design phase. I was looking for help in modeling a "product
availability" matrix.
Right now this is what I have came up with:
--product list
IF object_id('tempdb..#product') IS NOT NULL
DROP TABLE #product
GO
CREATE TABLE #product
(productid int
,class varchar(24)
,subclass varchar(24)
,priority int)
GO
INSERT #product VALUES (1, 'widget', 'standard', 1)
INSERT #product VALUES (2, 'widget', 'deluxe', 2)
INSERT #product VALUES (3, 'flange', 'standard', 1)
INSERT #product VALUES (4, 'flange', 'deluxe', 2)
IF object_id('tempdb..#customer') IS NOT NULL
DROP TABLE #customer
GO
--customers with active products
CREATE TABLE #customer
(custid int
,productid int)
GO
INSERT #customer VALUES (1, NULL)
--has nothing; products 1,2,3,4 should be available
INSERT #customer VALUES (2, 1)
INSERT #customer VALUES (2, 4)
--has 1 & 4, only product 2 should be available
INSERT #customer VALUES (3, 4)
--has 4, products 1, 2 should be available
Is this a sound design?
Am I going to run into problems if I add a third attribute like say color?
(i.e., widget, standard, blue).
Some time ago I remember reading an article on how to approach this problem
but I can no longer find it.
Any comments or insights are appreciated.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23z4DcuKZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> You haven't provided any DDL. I'm guessing but would the following be
what
> you need:
> select
> *
> from
> Widgets w
> where
> w.Priority >
> (
> select
> max (w2.Priority)
> from
> Orders o
> join
> Widgets w2 on w2.WidgetID = o.WidgetID
> and o.CustomerID = @.CustomerID
> )
> I'm assuming that the priority of a deluxe widget is greater than a
> standard.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Dave" <dave@.nospam.ru> wrote in message
> news:%23GC9eQKZFHA.612@.TK2MSFTNGP12.phx.gbl...
> I have a list of products with dependencies that determine what a
customer
> can purchase.
> Using a simple example, let's say I have 2 types of widgets, a standard
and
> a deluxe.
> If a customer who has not previously purchased any widget logs on to the
web
> site, he should see both the standard and deluxe widgets available for
sale.
> If a customer has previously purchased a standard widget, he should see
only
> the deluxe widget for sale. If the customer has purchased a deluxe
widget,
> he should not see any widget products for sale.
> Right now this logic is contained in a stored proc but I think it should
be
> stored in a table that would look something like a matrix.
> Has anyone worked with this issue before? If so, how did you handle it?
>|||This code will give you the numbers you expect:
select
*
from
#product p1
where
p1.priority >
(
select
isnull (max (p2.priority), 0)
from
#product p2
join #customer c on c.productid = p2.productid
where
c.custid = 3
and p2.class = p1.class
)
That said, you should still normalize the product table. You have a
transitive dependency, since priority is related to subclass. That then
gives you 2 tables:
CREATE TABLE #subclass
(subclass varchar(24) primary key
,priority int)
GO
CREATE TABLE #product
(productid int
,class varchar(24)
,subclass varchar(24) references #subclass
GO
Also, be sure to add primary key and foreign key constraints between
#customer and #product.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Dave" <dave@.nospam.ru> wrote in message
news:ug6iO7SZFHA.800@.tk2msftngp13.phx.gbl...
Thanks Tom.
I should have been more specific in my question. Right now I am still in
the design phase. I was looking for help in modeling a "product
availability" matrix.
Right now this is what I have came up with:
--product list
IF object_id('tempdb..#product') IS NOT NULL
DROP TABLE #product
GO
CREATE TABLE #product
(productid int
,class varchar(24)
,subclass varchar(24)
,priority int)
GO
INSERT #product VALUES (1, 'widget', 'standard', 1)
INSERT #product VALUES (2, 'widget', 'deluxe', 2)
INSERT #product VALUES (3, 'flange', 'standard', 1)
INSERT #product VALUES (4, 'flange', 'deluxe', 2)
IF object_id('tempdb..#customer') IS NOT NULL
DROP TABLE #customer
GO
--customers with active products
CREATE TABLE #customer
(custid int
,productid int)
GO
INSERT #customer VALUES (1, NULL)
--has nothing; products 1,2,3,4 should be available
INSERT #customer VALUES (2, 1)
INSERT #customer VALUES (2, 4)
--has 1 & 4, only product 2 should be available
INSERT #customer VALUES (3, 4)
--has 4, products 1, 2 should be available
Is this a sound design?
Am I going to run into problems if I add a third attribute like say color?
(i.e., widget, standard, blue).
Some time ago I remember reading an article on how to approach this problem
but I can no longer find it.
Any comments or insights are appreciated.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23z4DcuKZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> You haven't provided any DDL. I'm guessing but would the following be
what
> you need:
> select
> *
> from
> Widgets w
> where
> w.Priority >
> (
> select
> max (w2.Priority)
> from
> Orders o
> join
> Widgets w2 on w2.WidgetID = o.WidgetID
> and o.CustomerID = @.CustomerID
> )
> I'm assuming that the priority of a deluxe widget is greater than a
> standard.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Dave" <dave@.nospam.ru> wrote in message
> news:%23GC9eQKZFHA.612@.TK2MSFTNGP12.phx.gbl...
> I have a list of products with dependencies that determine what a
customer
> can purchase.
> Using a simple example, let's say I have 2 types of widgets, a standard
and
> a deluxe.
> If a customer who has not previously purchased any widget logs on to the
web
> site, he should see both the standard and deluxe widgets available for
sale.
> If a customer has previously purchased a standard widget, he should see
only
> the deluxe widget for sale. If the customer has purchased a deluxe
widget,
> he should not see any widget products for sale.
> Right now this logic is contained in a stored proc but I think it should
be
> stored in a table that would look something like a matrix.
> Has anyone worked with this issue before? If so, how did you handle it?
>