Please offer an opinion on Normalization Question

[penguin732901]

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.

[pietlinden]

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(Invoic eID)
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

[Cyberwolf]

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

[penguin732901]

One point I just realized in your posts - thanks, all - is that if
tblInvoices.ADI D 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.

[pietlinden]

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?

[david epsom dot com dot au]

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.goo glegroups.com.. .

One point I just realized in your posts - thanks, all - is that if
tblInvoices.ADI D 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.

[cjb_kjb]

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.

[penguin732901]

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.

[cjb_kjb]

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!!