var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
HELP - Challenging Data Modeling Problem!
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.
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...
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...
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?
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?
Last edited by Matt_T_hat; 01-26-04 at
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.