473,320 Members | 2,109 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Please offer an opinion on Normalization Question

A database keeps track of invoices (date, amount, balance, category,
etc) and advertisements (size, text, sponsored by, date entered etc)
(among other things).

Sometimes, an advertisement is sponsored by more than one person, so
each one gets an invoice for his or her share of the advertisement,
done by a button to an append query when the ad is created.

Thought 1: Store the ADID in Comment field of tblInvoices.

However, not reliable for linking as Comment is a memo field and can
store additional info (e.g. description of ad).

Thought 2: Add a field to tblInvoices to store ADID.

However, not all Invoices are associated with an Ad, so some space is
being wasted with this field.

Thought 3: use a linking table, invoiceID & AdID.

However, linking fields are usually used in a many-to-many
relationship. This relationship is one-to-many from tbIAds to
tblInvoices. However, the Invoices side can also have one or zero Ads
associated with it, while the Ad can have many invoices associated with
it.

Of course, this approach will also require more work on the part of the
programmer to link the linking table to every query using the AdID,
whereas the second method is simple.

Any further thoughts, comments, will be most appreciated.

May 23 '06 #1
8 1267
If you have multiple payers for a single invoice, you'd need a payments
table something like this:

tblPayments(
PaymentID --- Autonumber,
PayerID --- FK from Payer(PayerID)
PaidToInvoice --- FK from Invoices(InvoiceID)
Payment Amount --- Currency
Payment Date --- Date)

This would act as an intermediary table to break the "many invoices,
many payments" problem.

I would approach it from the query point of view. What kinds of
answers/summaries do you need to do on this information? If you build
a really small DB (a handful of records per table), you can
verify/validate your design before you go too far. you can check the
numbers manually and be sure your design will provide accurate answers
to your questions.

Sort of a roundabout answer, but in a nutshell, your final design
depends on the answers you need out of your database. (You might want
to read the Introduction in Access Developer's Handbook volume I. They
discuss that in the introduction, if I remember right.)

HTH,

Pieter

May 23 '06 #2
I use a one to many relationship all of the time in my DB's. I don't
see why you can't go that route, as you said it makes it much easier on
the programmer and it doesn't matter if there is nothing there as the
space used is probably very minimal and would not impact the DB size
significantly. Just my thoughts

May 23 '06 #3
One point I just realized in your posts - thanks, all - is that if
tblInvoices.ADID is allowed to be blank, I can't enforce referential
integrity. On the other hand, does a linking table help? Can I
enforce referential integrity in a three-way link?

As for payments, I already have that in tblAllocations - InvoiceID,
PaymentID, amount. This question is unrelated. It's only about
tracking the Ad that led to the Invoice. Payments are only tracked
with reference to the Invoice.

May 23 '06 #4
depends on which is the child record and which is the parent. If your
business rules allow/require you to create childless records, then you
can't use the built-in referential integrity constraints.

What do you mean, a 3-way link? (Example?) You mean the child has to
have three parent records?

May 24 '06 #5
You can use cascading updates, cascading deletes
and referential integrity on optional fields.

The only thing you can't do is set the 'required'
property, or use it as a primary key.

<pe***********@aol.com> wrote in message
news:11**********************@g10g2000cwb.googlegr oups.com...
One point I just realized in your posts - thanks, all - is that if
tblInvoices.ADID is allowed to be blank, I can't enforce referential
integrity. On the other hand, does a linking table help? Can I
enforce referential integrity in a three-way link?

As for payments, I already have that in tblAllocations - InvoiceID,
PaymentID, amount. This question is unrelated. It's only about
tracking the Ad that led to the Invoice. Payments are only tracked
with reference to the Invoice.

May 24 '06 #6
Yes the fact that a field can be null does not restrict the use if
Referential Integrity.

The best way to go in this case is to have ADID in the tblInvoices as a
non required field. (Just be aware that in any quereies that Join
Invoices and ADDs you may need to use Left or Right joins - eg if you
want all invoices and ADD info if it exists).

Just a point from above - There are not several payers for an invoice ,
there are (potentially) several invoices (and hence Payments) for an
Advertisement. It would however be a good idea to have a Client (or
Payers) table in any case.

May 24 '06 #7
tblInvoices tblInvoicesAds tblAds
InvoiceID -----------------> InvoiceID | --------> AdID
Amt AdID-------------|
AdType(Size)
Date
Source
CustomerID
Text
etc.

Where every Invoice does not necessarily have an Ad. For that matter,
it's possible an ad is a courtesy ad with no invoice. Furthermore, the
ad is created first then an invoice is generated so we I'm not sure
when (if) referential integrity can be enforced.

May 25 '06 #8
This isn't that complicated.

You don't need a tblInvoicesAds because there is not a many to many
relationship between Invoices and Ads (unless the original problem is
mis-stated) and an Invoice can be for many Ads as well..

AdID goes in tblInvoices. The fact that it may mostly be null doesn't
matter. The field can be defined as 'not required'. This doesn't stop
RI from working. If a value is put in AdID it must exist in tblAds, if
it is null - then that's ok. Cascade deletes will still work (if
that's what you want) as will cascade updates (Why you'd want that with
IDs as keys ???).

Now it is likely that an invoice can be for an Ad, sale of Stock, any
number of things so you could end up with a whole list of foreign keys
in the tblInvoices and it could be that only one should exist. Nothing
you can do about that - the relational model doesn't cater for it!!

May 25 '06 #9

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

Similar topics

3
by: JoeB | last post by:
I found an interesting thread on this from Nov., 2000, but it didn't fully answer my question. I understand normalization, but am trying to find the line between good database design and personal...
7
by: John Welch | last post by:
I have three tables and the way they are currently set up violates good normalization, but I'm having trouble figuring out a better way. Can someone suggest a more elegant solution? My tables...
59
by: Alan Silver | last post by:
Hello, This is NOT a troll, it's a genuine question. Please read right through to see why. I have been using Vusual Basic and Classic ASP for some years, and have now started looking at...
5
by: matthewtec | last post by:
I realize there are probably many discussions on these two... whether to get now or wait or yadda yadda yadda. My question is that I can continuing training on visual studio.net 2003, and...
20
by: hippomedon | last post by:
Hello everyone, I'm looking for some advice on whether I should break the normalization rule. Normally, I would not consider it, but this seems to be a special case. I have created an...
4
by: wideasleep | last post by:
I'm laying out a form and am in question on my normalization of data and I would appreciate a few opinions if this could be layed out better. I'm trying to follow a one to many all the way through on...
1
by: weird0 | last post by:
I know that is not the appropriate group for asking this question, but I do not know of any better forum. Can anyone plz explain clearly what normalization is? The first three normal forms upto...
22
by: sophia | last post by:
Dear all, can any one explain what is meant by pointer normalization given here:- http://c-faq.com/ansi/norml.html
4
by: FooFighter | last post by:
I was going to make a database to store a list of my DVD's. I have a question about the table structure though. I want to have some fields for actors. I'm thinking 4 or 5 would be plenty. From...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.