Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2003
    Posts
    3

    Modelling various classes of users

    In my relational db system, there are going to be various classes of users, however there are some relationships that need to treat these classes similarly.

    For example, my system can have Members and Staff. The information stored about these users is completly different except for some general fields like name and address, however I want both these classes of users to be able to post messages on the messageboard for example. Right now I have a table for Staff and a table for Member. I also have the usual thread->posts tables for the messageboard.

    I can think of several ways to do this, my question is, what is regarded generally as the standard, accepted way to approach a situation such as this one? This is for an assingment, so I think that the correctness of the design will matter more than speed or ease.

    Should I have something like a Person table that stores common information about both members and staff, and modify the member and staff table to only contain the extended information? This would mean that I can just store the PersonID for posts and threads on the messageboard.

    Or should I have a flag in a post which determines whether the ID is for a staff or a member? This deffinitly doesn't look like the correct way to me, but I could be wrong.

    The database will be managed in access and mysql.

    Basically I need to be able to model polymorphism in a sense. Any help is appreciated.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Modelling various classes of users

    You definitely want 1 table (Person) for the common data and relationships. The question then is, do you want to keep all the Staff-specific and Member-specific data in that table also, or have it in separate tables.

    The pure approach would be three tables:

    Person: id PK, type, name, address, linked to posting, etc.
    Staff: id PK REFERENCES Person, other details...
    Member: id PK REFERENCES Person, other details...

    A pragmatic approach (less pure, but simpler and probably more efficient) is to have just one table:

    Person: id PK, type, name, address, staff columns, member columns, ...

  3. #3
    Join Date
    Apr 2003
    Posts
    3

    Re: Modelling various classes of users

    Originally posted by andrewst
    You definitely want 1 table (Person) for the common data and relationships. The question then is, do you want to keep all the Staff-specific and Member-specific data in that table also, or have it in separate tables.

    The pure approach would be three tables:

    Person: id PK, type, name, address, linked to posting, etc.
    Staff: id PK REFERENCES Person, other details...
    Member: id PK REFERENCES Person, other details...

    A pragmatic approach (less pure, but simpler and probably more efficient) is to have just one table:

    Person: id PK, type, name, address, staff columns, member columns, ...
    Thank you,

    I decided to go with the pure approach. The system is for a university assingment, it will not actually go through practical use. The aim is just to show my understanding of the concepts, efficiency is not an issue.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •