Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2005
    Posts
    67

    Storing different entities in the same table

    I have to store employees and clientes of the company in a database.
    I now they are different entities.
    Normalizacion suggests that they shoul be stored in different tables. However, they have several common fields, such as member_id, password, email, addres, telephone, etc.
    Both of them can login into the sistem and do several things.

    The only difference is that they different information related.

    What is the best solution:
    1. Creating 3 tables.
    1.1 Table MEMBER for common information
    1.2 Table MEMBER_EMPLOYEE for storing information related to emploees
    1.3 Table MEMBER_CLIENT for storing information related to clientes
    And every time I need to retrieve information, it can be done by making an INNER JOIN between MEMBER and one of the other

    2. Creating only 1 table MEMBER and using only needed fields, leaving the others as null.

    Any idea or suggestion?
    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Can an employee be a client too?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    67
    Yes, because the idea is selling tickets and other stuff.
    The difference may be the price for instance.
    But the point is that employees and clientes have different information related and some common fields.

    Thanks!

    Quote Originally Posted by pootle flump
    Hi

    Can an employee be a client too?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually my answer depended entirely on that last question.

    Personally I would go for option one but only because that is the only way to avoid data duplication (what if an employee who is also a client moves house for example? or changes their name?).

    As far as the "common columns" argument goes - many of entities I deal with have a name attribute. However I (and I'm sure you) would never consider a schema with one table containing Name and then linking all my others to that. I think in terms of common data rather than common attributes.

    Just so you know - had you answered no to my question then my answer would have been no.

    Others may, of course, differ. That's why they call it the Internet

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2005
    Posts
    67
    That's right.
    I agree with you.
    Besides, they are two different entities (in the real life), that's why the should be two entities in the DB too :-)

    Thanks for your help.

    Quote Originally Posted by pootle flump
    Actually my answer depended entirely on that last question.

    Personally I would go for option one but only because that is the only way to avoid data duplication (what if an employee who is also a client moves house for example? or changes their name?).

    As far as the "common columns" argument goes - many of entities I deal with have a name attribute. However I (and I'm sure you) would never consider a schema with one table containing Name and then linking all my others to that. I think in terms of common data rather than common attributes.

    Just so you know - had you answered no to my question then my answer would have been no.

    Others may, of course, differ. That's why they call it the Internet

    HTH

  6. #6
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    What may be a better solution over all is a generalization hierarchy similar to your first solution. The member type will determine which subtype is used.
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  7. #7
    Join Date
    Sep 2005
    Posts
    23
    Quote Originally Posted by pootle flump
    Hi

    Can an employee be a client too?
    That is the key concept. If yes, I would put them in one table where a field would indicate their status, e.g Client, Employee, Both (if it exists).

    Besides, they are two different entities (in the real life), that's why the should be two entities in the DB too :-)
    Not necessarily. For instance men and women can be two separate entities but also one with common fields as humans and one field to indicate the different sex (m/w).

  8. #8
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    nst2

    That would not be normalised, and therefore it would not be a relational database.

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

  9. #9
    Join Date
    Jul 2006
    Posts
    2

    There should be different tables

    In my opinion, You should take 2 different tables for clients and employees. It has many reasons. First, the concept of normalization says that, second The activities which will be perform by the client and employee will be different so they would be in different tables. Another reason for that you have to query same table again and again by identifing a client or an employee and there are also chances of mistake in identifing. You will also unable to make foreign key in the situation of single table coz both entities will share comman primary key.

    Regards
    Sumit Soni

  10. #10
    Join Date
    Sep 2005
    Posts
    23
    Correct design does not always mean more Tables. If a client can be an employee and vice-versa, then there are no really clients and employees. We should know how this status changes to decide for that. What will you do if someone changes status? You will have to copy records from one table to the other.

    Also in the case of men and women, if both are clients of a store there is definitely no reason to put them in separate Tables. You simply put the sex in a field in case you want to include that kind of information.

    An example where I would have two tables for men and women could be a soccer championships for both men and women. Men and women never play together, therefore these are practically two separate actions and real life entities. In that case it's better to have two entities, even if the fields in both are equal (e.g. name, nationality, age, team, height, cards taken, penalties etc.)
    Last edited by nst2; 07-28-06 at 07:43.

  11. #11
    Join Date
    Sep 2002
    Location
    Sydney, Australia
    Posts
    255
    Certus' design (post #6) is definitely the best by far, given all the above posts, as it correctly (vis-a-vis normalisation) places the discrete and common elements (in one place) in the three separate tables.

    Re MemberType:
    - you do not need MemberType (this can be inferred by the join)
    - Even if you did implement MemberType, it should definitely not include a "Both"; instead, it should be "C"ustomer xor "E"mployee, AND the PK should by MemberId plus MemberType

    Re Column names, these should be the exact same name wherever they appear:
    - member_ID should be member_ID in all three tables
    - I prefer MemberId
    Remember:
    - it is the name of a column, a meaningful datum (foreign key), wherever it appears; it is not the name of a column in a particular table. The table in which they appear is already part of the expanded column name (eg. server.db.owner.table.column)
    - such columns (that appear in more than one table) are usually foreign keys, and you need to address them consistently.

    Consider the SQL required

    Cheers
    Derek Asirvadem
    Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
    Anything worth doing is worth doing Right The First Time
    Spend your money on standards-compliant development or spend 10 times more fixing it

Posting Permissions

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