473,562 Members | 2,931 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Table Design Question

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
Jul 20 '05 #1
4 4457
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
Jul 20 '05 #2
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 )
Jul 20 '05 #3
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.

Jul 20 '05 #4
On 11 Dec 2003 11:34:45 -0800, op***********@y ahoo.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
Jul 20 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
5933
by: mr_burns | last post by:
hi, is the table percent value for height used for displaying in browsers. i have a table i want to run to the bottom of the screen so it seemed best to set the height value to 100%. when i display the table, it only sizes to the elements nested inside the table (as if no value for height has been given) and not to the bottom of the...
0
1409
by: 2trax | last post by:
Hi all, I am trying to mimic the unix security model (users and groups) in a web app. I have thought of two ways of implementing this, but am unsure which would be better. The first design utilises three tables, while the second utilises two tables, however one of the columns in the first table is an an array.
15
2131
by: kimi | last post by:
I have just started working on a project that is partially complete. It is an application that is using access to store test results. The test results are being stored in two Access 2000 databases. DB #1 = StudentDB DB #2 = TestResulstsDB Why are there 2 dbs? I do not know - but that is one of the tings that we will be changing....
5
3730
by: Sami | last post by:
Please bear with me, and if you answer this question, please do it step by step. I am new at Access, not at all sophisticated. I am using Office XP. This will need to be read in Access for Office 2000. I am creating a database to track student athletes. I have created the following tables. The table title is to the far left, with fields...
6
4829
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time
18
3774
by: TORQUE | last post by:
Hi, Im wondering if anyone can help me with a problem. I have a form with more than 50 unbound fields. Some of the fields will be blank from time to time. This seems to be where im having trouble. I have tried keeping some of the fields bound and when I use the save button it has been saving as 2 different records. This is unacceptable. ...
7
4857
by: david | last post by:
I have asked this question before. But it does not work for me. Given radion buttons in the web form design page. What I did is described as follows. A panel control is dragged into the design form, and a table control is dragged into the panel from tooolbox. Add cells for the table through the properties. Now I cut a radio button, and click...
1
1504
by: Mark | last post by:
Hi there gurus, can you please add your 2 cents on this design? We're having trouble relating these tables in a diagram because of the keys. Is it necesary to have the references setup? I would assume yes so the forign keys can be setup. If you look at this link, you'll see our diagram. In Red are the relationships that we would like to...
4
1955
by: yanjie.ma | last post by:
Hi, I've got a two part question on table and form design (sorry for the length but it takes a bit to explain). Our sales department uses a look-up table to help the them select the best equipment to use for a customer's needs. It basically consists of a table with the following setup: Equipment: Option1: Option2: Option3: ...
54
3980
by: csolomon | last post by:
Hello: I was wondering if I could get some input on how to address a design issue, involving my composite table. I have one portion of my project complete. The following forms and reports I will add, piggyback off of my existing design. The part I have already completed allows my users to create a design sample; this is made up of...
0
7577
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7627
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6221
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5477
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5193
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3608
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2073
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1191
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
903
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.