Adding Columns vs 1-To-Many

[Murp]

I have a database of books, and I need to store information such as the US
Price, the UK Price, the Australian Price, etc. only each book does not
strictly have a price for each of these countries. Would it be better to
create these fields in the main table, and leave them blank where the field
doesn't apply, or join another table, Foreign Price, with a one to many
relationship, and have two fields, Price and Country?

 

[TC]

It depends on what you mean by "better".

Putting them in the main table would clearly be /simpler/. But then, it
wouldn't be easy to add new country prices. You'd have to add new
fields to the table. An ordinary end user should not be permitted to do
that, on the good chance they would stuff it up. So they would have to
come back to you, just to add new country prices. But it is a simple
approach & only needs one form.

Using a seperate table is more correct in terms of having a properly
normalized table design. And you could easily have a form where the
user could define new countries & respective prices. (You wouldn't need
to add more fields for that.) But this approach is a bit more complex
than the previous method. For example, you'd probably need two forms:
a main form for the book, and an embedded subform for the country
prices. Also you would have to define the 1:m relationship in the
relationship window.

Personally, I would unhesitatingly go for the second approach. It's a
bit more work up front, but waaaaay easier & more flexible down the
track. You do 1:m relationships so often, in most datbase, that you can
set it all iup in your sleep, once you have done a fair number of them.

HTH,
TC

 

[TedMi]

I resoundingly support TC's recommendation of normalizing the table, using a
related table to hold prices for any number of countries. The other approach
causes nothing but grief. And it is not necessarily simpler. To create
reports in the additional column approach, you would have to write a seaprate
query for each country for which you had a column. The related table could be
processed with a single query for any number of countries. But that related
price table needs to have at least 3 fields: Book ID (that's what ISBN is
for!), country, price.

 

[Murp]

You both raise good points, and it was the way I was leaning towards myself.
My main concern is if I have more than one of these situations in the one
table, bringing it all up as a crosstab query becomes problematic. I assume
it would require setting up separate crosstab queries for each relationship,
and then joining them in one main query.

 

[TC]

It is inevitable, in a properly designed relational database, that you
will have to join multiple tables. That's the nature of the beast!

For example an "invoice" (which is one thing from the user's viewpoint)
would probably come from at least 5 tables: Invoice, InvoiceHeader,
InvoiceLine, Product, Customer, and maybe more.

So it's wrong to think: "Oh, I shouldn't really create a new table, I'd
better add this data to an existing one, or it will get problematic!"

HTH,
TC

 

[TC]

Hmmm, no need for Invoice & InvoiceHeader. One of those is quite
enough. So I'll add ProductPriceHistory, to justify "at least 5 tables"
!

TC