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 4 4460
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
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 )
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 screen. i have also tried appling the height attribute
as a css class but no joy.
|
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.
|
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. Combining all of the data into one database.
|
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
under each. The common field will be the StudentID field, which is
their student number assigned...
|
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
| |
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.
This is what I have, can anyone help me out with this?
|
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 the table (note I can not
select a cell), then right click Paste. The button is not in expected...
|
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 make for referential integrity, but
cannot because of the keys....
|
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: Option 4: ...
-------- ------- ------- -------- --------
|
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 several materials to create one sample. I
have accomplished this using this design:
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |