Table Design Question

[Opa1]

I am designing and product ordering database and have a design
question.
I have a Payments table which records all payments for a given
OrderID.
There are several payment methods (cash, credit card, check). Each of
these
payment methods has their own fields (i.e. CreditCardNum for payments
by credit card, CheckNum for payments by check, etc.)

If I place the CreditCardNum and CheckNum fields into a payments
table, it will not be correctly normalized (i.e. nulls will exist for
CheckNum when payment by credit card and vice-versa.)

So what should I do? Create separate payment tables for each type
payment method?
Does anyone know of a good database schema/framework which models a
typical ordering system (Orders, Payments, Invoicing, etc)?

Thanks

[Erland Sommarskog]

Opa1 (op***********@yahoo.com) writes:

I am designing and product ordering database and have a design
question.
I have a Payments table which records all payments for a given
OrderID.
There are several payment methods (cash, credit card, check). Each of
these
payment methods has their own fields (i.e. CreditCardNum for payments
by credit card, CheckNum for payments by check, etc.)

If I place the CreditCardNum and CheckNum fields into a payments
table, it will not be correctly normalized (i.e. nulls will exist for
CheckNum when payment by credit card and vice-versa.)

So what should I do? Create separate payment tables for each type
payment method?

Real-world data does not always go well in hand with ideal data models.

In an object-oriented model, you would have a virtual base class
"payments", and then subclasses for each method that you could
instantiate.

Of course, you have have a main table, and the sub-tables for each payment
method, but this quickly goes out of hand. Then again, if you have 12 sub-
classes with over 100 attributes, and lump into all table, then things
also gets out of hand.

So in the end, you need to be pragmatic. For this case, it sounds like
one single table would be the best.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Anith Sen]

For such problems, classic relational model provides type inheritance on
entity types whereby you have subtypes with inherited attributes from the
super type. Date's Introduction to Database systems, Chapter 13.9 (Entity
subtypes/super types: Pg. 433 on 7th Ed) has an excellent description of how
the model provides a clean solution using views to support logical
independence on base relations.

However, there is no *clean* way to do this in SQL even with sub table/super
tables. The recent SQL standard (SQL3) adds syntactic sugar with DDL for
super tables & sub tables. Note that these are semantic shortcuts and
provide no facility for type inheritance and hence SQL based DBMSs cannot
proactively provide support for such relationships.

For a practical solution, identify if payment methods are time-varying. If
they are, in other words, if, in the future, there could be other payment
methods other than cash, credit card & check, opt to represent them as
column in the payment table. Obviously you will have to deal with Nulls and
certain fact-based anomalies depending on the additional attributes like
CC#, check# etc.

If they are static, opt for the super type/subtype relationship, whereby you
can avoid Nulls and potential anomalies. However, beware that if there are
too many payment methods, a large number of tables to support super type/sub
type relationship can make the schema really clumsy.

--
- Anith
( Please reply to newsgroups only )

[Opa1]

Thanks. I will take your advice.

Any info on a database framework/schema that I can modify for my
product ordering system?
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn*********************@127.0.0.1>...

Opa1 (op***********@yahoo.com) writes:

I am designing and product ordering database and have a design
question.
I have a Payments table which records all payments for a given
OrderID.
There are several payment methods (cash, credit card, check). Each of
these
payment methods has their own fields (i.e. CreditCardNum for payments
by credit card, CheckNum for payments by check, etc.)

If I place the CreditCardNum and CheckNum fields into a payments
table, it will not be correctly normalized (i.e. nulls will exist for
CheckNum when payment by credit card and vice-versa.)

So what should I do? Create separate payment tables for each type
payment method?

Real-world data does not always go well in hand with ideal data models.

In an object-oriented model, you would have a virtual base class
"payments", and then subclasses for each method that you could
instantiate.

Of course, you have have a main table, and the sub-tables for each payment
method, but this quickly goes out of hand. Then again, if you have 12 sub-
classes with over 100 attributes, and lump into all table, then things
also gets out of hand.

So in the end, you need to be pragmatic. For this case, it sounds like
one single table would be the best.

[Mike Sherrill]

On 11 Dec 2003 11:34:45 -0800, op***********@yahoo.com (Opa1) wrote:

[snip]

There are several payment methods (cash, credit card, check). Each of
these
payment methods has their own fields (i.e. CreditCardNum for payments
by credit card, CheckNum for payments by check, etc.)

If I place the CreditCardNum and CheckNum fields into a payments
table, it will not be correctly normalized (i.e. nulls will exist for
CheckNum when payment by credit card and vice-versa.)

So what should I do? Create separate payment tables for each type
payment method?

It sounds like you're describing a supertype/subtype issue. Google
the database newsgroups for "supertype" and "subtype". In a (possibly
misleading) nutshell, attributes common to all subtypes (say, amount
tendered) go in the supertype's table; attributes unique to the
subtype (say, check number) go in the subtype's table. For 'n'
subtypes, you're looking at 'n' + 1 tables, 'n' views (each view joins
one subtype to its supertype), and a slew of CHECK constraints.
--
Mike Sherrill
Information Management Systems