2 tables or just 1

[LittleB]

Hello all!!

I wonder if anyone can help me and I hope I am posting in the right place, forgive me if not.

I have a fairly complicated scenario but to simplify it, I have a Customer table which is connected to Order table.

Staff members can be customers and they get staff discount for their orders.

How do I design my tables to show this? Do I store the same staff data again in Customer table or is there a way these can be connected. N.B. Extra data is collected about staff.

May be the solution is very simple and I am missing it because I thought too much about it, so ANY suggestion is very welcomed :)

MANY THANKS!!

[JFunk]

Hi little B,

I'm no expert but if you add the names of your staff who are also a customer in the customer table and add a field/column "STAFF". You can set this as a flag for "yes or no". If flag is Yes, then you can use this criteria in the calculation for discounts.

Hope this helps,
Funkie

[timothybard]

I would recommend the following partial database structure:

People: Id, Name, Address, Etc...
Client: Id, PeopleID, Etc...
Staff: Id, PeopleID, Etc...

The People table will store basic info about each person. Any staff specific information will be stored in the Staff table and any client specific information will be stored in the Client table. This structure will reduce the redundancy (each person is recorded in the database once, so if someone moves or changes his/her name, the data needs to be changed in only one place). Also, you can determine if a Client is a Staff member by joining the Client table to the Staff table on the PeopleID and checking if Staff.PeopleID is null.

My suggestion certainly adds a layer of difficulty in designing and programming the database, but it does adhere to the rules of normalization and, in the end, will make things easier for the end-user.

[michaelbdrell]

We must assume that an employee could become an ex-employee and a customer could become an employee. With more than a small number of employees and a single location, the safest approach is to use a status table that contains the start and stop date of employment. Sales are straightforward (status on the day of transaction controls eligibility for discounts) -- refund transactions require the additional information (past status).

Two tables.

[SKANK!!!!!]

i think u could just get the information from the two separate tables. staffs and non staffs. its not that hard. keep them seperated.
if a staff is a customer that doesnt matter. they are still staff. if u added the staff to the customer table that would not work because they are staff.
if being staff doesnt matter than u can make one table with staff and customers and then have a status but that might not work if staff and customers have different values and stuff.
its best to keep them seperated and it there is a staff that is also a customer because there is a employerr discount then if theres anough of them u may want to make a third table for the employee discount thing or whatever. and those can be like the customer/staff things combined.
unless u want to make one big table with them all? i dont know about that
maybe.

[timothybard]

The most "proper" way to set this database is to have one table that stores all the people. Using two tables would not follow normalization rules.

The table that stores the people would only store the basic information about each person.

You then need two additional tables: one that lists the clients and the other to list the employees. This table would also store any client and employee specific information.

Lastly, you would need two additional tables. The first would be used to store the employee status (date, status fields). This will record when an employee is hired, fired, quits, retires, etc. The second table is a client status table, if that is relevant. The client status table can store when someone started being a client and when they ended being a client.

Therefore, you only need one table to store each person, but you need several additional tables to store all the other relevant information.

[SKANK!!!!!]

sure or u cn do it that way if u havent already made the database tables