I'm thinking there has to be a better way...

[mswscco]

I'm trying to design a database for a business that (let's say) furnishes executive offices at large companies. Each contract is negotiated, so what Company ABC pays for a chair is not necessarily what Company XYZ pays.

I'd like the solution to be expandable by the user without getting into the schema, so I'm thinking:

CUSTOMERS table with Customer and CustomerID

ITEMS table with Item, Price, CompanyID and NegotiatedPrice.

LINEITEMS with Item, Price and NegotiatedPrice, the latter 2 as lookups from ITEMS

Each time a new contract is signed, the user can add a new record to the CUSTOMERS table. I'd have to script a duplication of all records in the ITEMS table with the new CompanyID and blank NegotiatedPrice for user to fill in.

If the user wants to offer a new item, a new record can be created in the ITEMS table and I'd have to script as many duplications of that item as there are Customers

It's this duplication process that bothers me a little bit. With 20 different contracts, the ITEMS table will have 20 items called "Corner Desk" 20 items called "Guest Chair" 20 items called "Wall Clock" etc. albeit with different CompanyID and NegotiatedPrice

Is there a better way to go about this? TIA to all.

[Peter (duksis3)]

I think that your way is OK.

Later you will find very useful to see what each company was get.

Or what are the best sellers. Or...

From my experience - better is to record maximum information.

In similar way I have made store application with several thousands of units, tens of suppliers, hundreds of buyers and don't think that buy and sell price is the same all the time. In the end of day, week, month or year I can get reports in any form or layout my boss can imagine.

Don't worry and keep developing :laugh:

[David McQueen]

If I am reading this right, it is not a particularly good structure.

Customers- Name rank ID....

Items - Name, ID

Contract - Customer ID, boiler plate - Many to one relation with Customers assuming a customer can have more than one contract.

Line Items - Item ID, negotiated Price, Contract ID - A join table of Contract and Items

I think this would give a better more normalized structure.