I am a fresher and I am involved in designing and developing a database. Please critique my er diagram
These are the following tables
client-----auto generated Primary key(clientid)
vendor----auto generated Primary key(vendorid)
clientvendor---composite primary key(clientid, vendorid)
employee----composite primary key(clientid and employeeid)
invoice(aphistory)----composite primary key(vendorid and invnumber)
aplid(invoicelineitem)-----composite primary key(invnumber, vendorid, invlinenum)
purchaseorder----composite primary key(clientid and ponumber)
product-----productid(auto generated primary key)
contract---contractno(auto generated primary key)
1. Data comes from diff sources so I decided on having a combination of auto generated and composite primary keys throughout the database. I have been doing a lot of research from books and websites; and I am getting a mixed response on whether to use a surrogate key or a natural key. Could you please let me know If i am on the right path based on the attached er diagram.
2. Product details repeats in purchase order, invoice and product master. I thought of just using the product master table for product details. But the business requires product details to repeat in purchase order and invoice for comparison purposes to catch potential fraud committed by employees.
This rule is resulting in a lot of data redundancy. I have tried normalizing all the tables till the 2nd normal form, and I am not sure how further shd i normalize.
Please do bear with me; as I am a fresher and my questions may sound silly. Thanx in advance for the help.