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!

Hi

Can an employee be a client too?

pootle flump ur codings are working excelent.

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!

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

pootle flump ur codings are working excelent.

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.

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.

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).

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).

nst2

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

Cheers

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

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.)

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