Hi,

I'm writing an e-commerce application and have got my mind in a twist trying to deal with a fairly simple problem... any comments on my proposed solution and ways I could improve it would be greatly appreciated - I have tried going through the steps of normalisation and seem to have created a monster!

Problem:
Each school listing items for sale can relate those items to an Organisational Unit (eg. Year 7; Football Team; HouseA etc.) these OUs can be added / removed at the schools will. In turn the 'users' for each school can belong to the OUs for their school.

Solution:
The bits i'm happy with....
tblStock (pkStockID, Item, Description.....)
tblUsers (pkUserID, Forename,.......fkSchoolID)
tblSchool (pkSchoolID, name, address........)

The proposed mapping of stock & users to organisational units:
tblOrgUnit (pkOU_ID, fkSchoolID, unitName)
tblUser2Org (pkUserID, pkOU_ID) (Joint primary key, foreign keys to 1st tbls)
tblStock2Org (pkStockID, pkOU_ID) (As above)

Whilst this does link them all together, I'm concerned that Many items of stock can belong to many OUs and many users can belong to many OUs - have i dealt with it correctly? Also the tblUser2Org and tblStock2Org will be massive once a few large schools are using the system - is there a better way to deal with large amounts of 'linking' data?

I hope this makes some kind of sense, thanks in advance for any advice or approval.

Kind Regards, Jason Woollard

I'm not clear whether you mean that a User should or [b[should not[/b] be linked to many OUs? Certainly your design allows that to happen. If you meant that each User should only be linked to one OU then the design would be:

tblUsers (pkUserID, Forename,.......fkOUlID)

Assuming you want the many-to-many that you have, there is no better way to design it. You don't say what DBMS you will be using, but if the data volumes are "massive" then it better be something robust, in which case handling large data volumes will be a piece of cake.

I would never prefix table names with "tbl" personally. Smells of VB code!