Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SMH_999
Starting Member
1 Post |
 Posted - 2010-02-10 : 15:36:30
|
| Hi All,I've hit a design challenge that I'm not sure how to handle. After advice really.My system, which is in the design phase needs to have:- Organisations - Which have 1 - many Users- Organisations also have 1 - many AssetsProblem is, users may also exist as an entity outside of an organisation (A user would be EITHER on its own - e.g. Member of public OR a member of an organisation - Never both)For this post, I'll call the users who are not members of an organisation 'Public Users'- 'Public Users' also have 1 - many Assets- Owners - A concept to sum up organisations and/or public users who have 1 or more associated Assets, need to be reported on against assets, e.g. List all 'Owners' and show there associated assets.So - How would my schema look for this?So far I have:TblOrganisations - PK/FK - TblUsers(Defines organisation with users)TblUsers - PK/FK - TlbAssets(Defines public user with assets)TblOrganisations - PK/FK - TblAssets(Defines organisation with Assets)And thats where I got confused and realised perhaps my design wasnt working.Help! :)Simon. |
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-02-12 : 17:24:49
|
| CREATE TABLE Owner (OwnerID INT NOT NULL PRIMARY KEY, ...);CREATE TABLE Organisation (OwnerID INT NOT NULL REFERENCES Owner (OwnerID), ...);CREATE TABLE User (OwnerID INT NOT NULL REFERENCES Owner (OwnerID), ...);CREATE TABLE UserOrganisation /* users who belong to orgs */ (UserOwnerID INT NOT NULL REFERENCES User (OwnerID) PRIMARY KEY, OrganisationOwnerID INT NOT NULL REFERENCES Organisation (OwnerID)...);CREATE TABLE AssetOwner /* Users/orgs who own assets */ (OwnerID INT NOT NULL REFERENCES Owner (OwnerID), AssetID INT NOT NULL REFERENCES Asset (AssetID), ...); |
 |
|
|
|
|
|
|
|
Users, Assets, Organisations