Data design issue. Need help with normalization

Question

I am developing one web based payment system. I am facing problem in database design.

Following are tables and their fields:

Customer: cust_id , name 
Supplier: supplier_id, name 
Employee: employee_id, name 
Expanse: expanse_id , name
group: group_id, group_name 
(groups are: {1,Customer},{2,Supplier},{3,Employee},{4,Expanse})

and there will be one more table:

payment: payment_id, group_id, name(ids), amount

In above table, name field will contain id of respective group. Like if group id is 1 and name is 15 that is customer whose id is 15 (customer_id=15)

I want to know that above tables satisfy 3NF? if not then what is the correct way.

Also i want to know that if i want list of all the payment, which will have fields like: payment_id, group_name, name_of_person/expanse. What will be the query to get this list.

• paparazzo: Not 3NF to me. That would be some ugly queries.
• Rick James: "In above table, name field will contain id of respective group. Like if group id is 1 and name is 15 that is customer whose id is 15 (customer_id=15)" -- This is too cryptic. Please show sample data, and show the query.
• Kartik Parekh: Customer table data ({1,'Ana'},{2,'John'},...,{15,'Tim'}). Supplier table data ({1,'Mark'},{2,'Sophia'},...,{18,'Elizabeth'}). payment table data ({1,1,15},{4,2,18}). Then list of payment should be: ({1,'Customer','Tim'},{4,'Supplier','Elizabeth})
• Walter Mitty: What are the FDs? Without that, questions about normalization are mere speculation.

Answer 1

One of these is not like the others - expanse

Entities:
groupID, ID, name

composite PK on groupID, ID

Answer 2

(As non-native English speaker, "expanse" is not immediately clear to me)

I'd join the four tables (customer, supplier, employee, expanse) to a single table

(id, group, name)

Depending on your needs, id could be unique key, so group would be only foreign key for linking group. In this case group can be dropped from payment.

• ypercubeᵀᴹ: It's likely a typo of "expense".