I have been given the task of creating a fairly well normailized relational database. [Products] is our main table. The other is [Price_Band] and allows a number of name bands to be invented now the difficult bit. I need have the database hold priceband information for every price band for every product. If a new product is invented it should have the [Price_Band] defaults and if a new Price Band is added then all products should have the default settings.

So how do I create a normal for this mess? I need a working ERD that I can impliment fairly swiftly.

Any ideas?

product -----------<- prodpriceband ->----------- priceband

a typical many-to-many relationship

the business about new products getting default bands and new bands being added to every product, well, that's all done with application logic

So what your saying is that

For Each [Product] do.sql(For Each [Band] make table)

or some such...

Darn

What would the SQL look like for ensureing no duplicates of a band for a product and checking for the absence of said bands?

Can a Query not be made like:

Select * from bands unless same exists in exeptions table?

to ensure no duplicates, declare a unique constraint

in the case of the prodpriceband table, the primary key will suffice, since it is unique

create table prodpriceband
( prodid integer not null
, pricebandid integer not null
, primary key (prodid,pricebandid)
, constraint p_fk foreign key (prodid) references product(id)
, constraint pb_fk foreign key (pricebandid) references priceband (id)
)

Thankyou that might just work...

I'm not sure how easy this will be as that'll be a first for a primary key I'm useing...

Thanks again

-Matt

The real beast is that I have to do this in Access 2000...

Anyone have thoughts on the combined key?

thoughts on the combined key?

there can be only one thought: that is the right way to do it

did you have a specific concern?

Yes, sorry...

Specific concern: how the heck to talk an MS-Access database into doing that.. if it can be done with one at all.

Should have made myself clear to start with.

Thanks for advice it makes a lot of sence.

EDIT: Hmm I see, I have now created said table... not so hard really. The question that troubles me is how do I treat this table when creating queries?

if you have access to (no pun intended) the MDB file, just open it, open the table in Design view, highlight both columns, and click on the little key -- this makes them a composite primary key

if you do not have access to the MDB, you will have to talk to it using DDL -- see Common DDL SQL for the Microsoft Access Database Engine

as for your other question, "how do I treat this table when creating queries?", i am afraid you will have to be more specific, i don't know what you mean by "treat"

I think I was worrying too much my friend.

Thankyou for your help it has been greatly apreciated.