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.

  • Not 3NF to me. That would be some ugly queries. – paparazzo Sep 18 '16 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 Sep 19 '16 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 Sep 19 '16 at 1:57
  • 3
    What are the FDs? Without that, questions about normalization are mere speculation. – Walter Mitty Sep 19 '16 at 10:39
0

One of these is not like the others - expanse

Entities:
groupID, ID, name

composite PK on groupID, ID

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.

  • It's likely a typo of "expense". – ypercubeᵀᴹ Sep 20 '16 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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