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.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Users, Assets, Organisations

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 Assets

Problem 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), ...);
Go to Top of Page
   

- Advertisement -