0

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.

Improve this question
4
  • Not 3NF to me. That would be some ugly queries.
    – paparazzo
    Commented Sep 18, 2016 at 16:44
  • 1
    "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.
    – Rick James
    Commented Sep 19, 2016 at 0:04
  • 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})
    – Kartik Parekh
    Commented Sep 19, 2016 at 1:57
  • 3
    What are the FDs? Without that, questions about normalization are mere speculation.
    – Walter Mitty
    Commented Sep 19, 2016 at 10:39

2 Answers 2

Reset to default
0

One of these is not like the others - expanse

Entities:
groupID, ID, name

composite PK on groupID, ID

Improve this answer
0

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

Improve this answer
1
  • It's likely a typo of "expense".
    – ypercubeᵀᴹ
    Commented Sep 20, 2016 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.