Hi all,

i am designing the backend for a website that has several different sections and several different types of users.

E.g

General Users
Suppliers
Estate Agents
Seller

now i am having difficulty thinking of how i can create a system that would allow me to administer all these different types of user. My problem is, i have all these entities in seperate tables i.e.

tblUser
tblSuppliers
tblEAgents
tblSeller

i had the thought of basically creating one user table that encapsulates all user details, and then have these link into the other sections, for example if could just add UserID as a foreign key to the employers table and thus have a defined link between the two. That way all users are in one place and i can administer roles and permissions centrally, my only concern is if this is a legitimate system to use, and i just want some other Database minds to have a look at it.

Any Suggestions??

Roberto

Yes, as your explanation says, Suppliers, Estate Agents and Sellers are just kinds of User and so should have their common data (including UserID) defined in a single table Users (not tblUser please! This is a database, not Visual Basic ) You may (or may not) still want separate tables Suppliers, EstateAgents and Sellers for the non-common information.

Sounds good to me too!

And here's a quickie page I did that explains the same concept:

ASP Design Tips - Login Page
http://www.bullschmidt.com/devtip-loginpage.asp

Thanks all,

i have another question now. My inital plan of centralising the users into one table is the way i am going to go, however my new concern thinking of what may happen in the future (As i know it will change ) is the fact if i link the user id to the employer or landlords table i am then restricted to having to create a new record if they want a new user login(they may want to assign different roles to different users, admins, general users etc) so i need to think of a new solution. My thinking at the moment is such:

User can belong to Zero(becuase its a general user table) or One Employer
Employer can have One Or Many users

Now my thought now turns to adding an EmployerID FK to the User table, however, i dont like that becuase there are other fields that will then have to be placed into the table. For Instance landLords has the same relationship with users as Employer, so all of a sudden i now have
EmployerID and LandLordID as new columns in my table, obviuosly how i set it up in SQL Server will dictate how the relationships will be forced(or not as the case may be), but as i said i dont like this. I was thinking of creating a join table between Users and tables related, for instance:

tblSiteUsers
ID
Etc...

tblEmployers
ID
Etc..

tblEmployerSiteUsers
ID (Just to keep it Unique but probably not needed)
EID (EmployerID)
SUID (SiteUserID)

The thing is with this, its not a many to many relationship so i dont think i really need it for structure, more for neatness on my part.

does anyone have any views on this, otherwise it means implementing it and hoping for the best!! hahhahah! oh no!

Any suggestions would be helpful!

Rob

Anyone??

sounds good to me

Well I must admit I started getting lost in all that, which is why I didn't respond before. However, I suppose any response is better than none, so...

Perhaps Employer and Landlord are 2 subtypes of a more general entity. e.g. Party? (Party is often used as the name for an entity that may represent a Person or an Organisation in different cases - e.g. an Employer could be Microsoft or it could be Mr John Smith).

So now the relationships between User and Employer and between User and Landlord become 2 relationships between User and Party:

create table party( party_id ... primary key, party_type ... );
create table employer( party_id references party primary key, ... );
create table landlord( party_id references party primary key, ... );
create table user_relationship( user_id, party_id, ... );

Hi there,

Thanks for the responses!! i have concluded on a structure now, and trust it not to be the one i discussed earlier. The way i am going to do it this.

1) Create a central Users table that will take in all the user information, name, address, Uname, Pword etc.

2) Create seperate tables that deal with Employers and LandLords etc to take in their personal details.

3) Link them by adding EmployerID and LandlordID into the User table

Now i didnt really want to do this, however, the more and more i racked my brain i kept comming up to the same conclusion, and that is that all relationships between user and say Employers and Landlords is one to many! i cant invent a many to many.

Now i do have a centralised table and i can link users to either a employer or a landlord. My problem is i now create an extra <NULL> value if that person is not a memeber of say employers or Landlords, and two <NULL> values if that user is just a general site user.

This site is just huge so the db is complicated, i am having to rework areas of my initial design, which is something i didnt want to do.

One last time, can i legally do this, or am i just asking for trouble? I apologise for all the questions, i just want to get this right!

Rob

Have a "People" table. Put EVERYONE in it.

Have a "Role" table. Owner, Landlord, Tenant.

Have a "Property" table. Put each property into it. An attribute for property is perhaps "for sale".

Have an Xref table.

Register a person into the Xref table. Put their peopleID, the Role they play and for which property. Put a start and end date and whatever other attributes needed.

Given this model, a person could be potentially a tenant AND a landlord.

Make the three keys unique - a person can't be a tenant twice. If it is apartments where one person CAN be a tenant twice then put an extra column in the relationship table and call it "address" or "room number" or something.