ER diagram of auditing database design

[tammy2512]

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

[tesuji]

Hi

well, understanding a data model only by names of listed tables (entities) and keys where some of them express vague relationships is hardly feasible. You should describe verbally what are client, vendor, employee, what are their relationships? One cannot review your keys without knowing that meta-data. Nobody knows whether "employee" belongs to client or vendor or somthing else, why "contract" hasn't any relationship etc.

However that be, let me annotate somewhat:

>>> employee----composite primary key(clientid and employeeid)
Means, that every time the (physically) same employee is to associate to a new client the data of this employees must be duplicated/copied, what's obviously wrong. employee's pk should be employeeid only.

If there is an 1-to-many relationship between employee and client, pk of employee becomes non-identifying foreign (nfk) key of client. That means a certain client would have one permanently assigned employee (salesman?).

If the relationship is many-to-many (what is more likely), a linking table clientemployee is necessary, where the pk of this special table is at least (clientid,employeeid).

>>> Same for invoice(aphistory)----composite primary key(vendorid and invnumber)
pk should be invnumber, vendorid is nfk to invoice. Assuming your pk(vendorid, invnumber) were correct, then a certain invnumber which makes the causal invoice could have been assigned to various vendors.

>>> aplid(invoicelineitem)-----composite primary key(invnumber, vendorid, invlinenum)
pk is (invnumber, invline#) only! Because invoicelineitem is already assigned to an invnumber, the corresponding vendor is uniquely identified.

>>> purchaseorder: analogous to invoice

>>> product: ok

>>> contract: unclear, not used

Data models can be drawn with ERM tools, for example with Msql workbench, a nice tool to visualize entities and their relationships (I myself use Sybase tools, though they cost some Dollars).

btw, where is your attached diagram?

-- tesu

[tammy2512]

Hi

well, understanding a data model only by names of listed tables (entities) and keys where some of them express vague relationships is hardly feasible. You should describe verbally what are client, vendor, employee, what are their relationships? One cannot review your keys without knowing that meta-data. Nobody knows whether "employee" belongs to client or vendor or somthing else, why "contract" hasn't any relationship etc.

However that be, let me annotate somewhat:

>>> employee----composite primary key(clientid and employeeid)
Means, that every time the (physically) same employee is to associate to a new client the data of this employees must be duplicated/copied, what's obviously wrong. employee's pk should be employeeid only.

If there is an 1-to-many relationship between employee and client, pk of employee becomes non-identifying foreign (nfk) key of client. That means a certain client would have one permanently assigned employee (salesman?).

If the relationship is many-to-many (what is more likely), a linking table clientemployee is necessary, where the pk of this special table is at least (clientid,employeeid).

>>> Same for invoice(aphistory)----composite primary key(vendorid and invnumber)
pk should be invnumber, vendorid is nfk to invoice. Assuming your pk(vendorid, invnumber) were correct, then a certain invnumber which makes the causal invoice could have been assigned to various vendors.

>>> aplid(invoicelineitem)-----composite primary key(invnumber, vendorid, invlinenum)
pk is (invnumber, invline#) only! Because invoicelineitem is already assigned to an invnumber, the corresponding vendor is uniquely identified.

>>> purchaseorder: analogous to invoice

>>> product: ok

>>> contract: unclear, not used

Data models can be drawn with ERM tools, for example with Msql workbench, a nice tool to visualize entities and their relationships (I myself use Sybase tools, though they cost some Dollars).

btw, where is your attached diagram?

-- tesu

Thanx for the reply!!! I apologize for being unclear; The reason for which i selected a composite primary key:

For example I take the relationship between client and employee....We have multiple clients sending us data. so if we use client generated employee id as a primary key. The id may overlap between multiple clients; but employee id is unique to each client.

All the above data which we get just sits in the database; without any updates or changes in the data.

Some of the notes:
1) Client issues purchase orders
2) Client has many vendors; and each vendor can belong to many clients.
3) Vendors generate invoices.
4) Employees of client enter purchase order and invoice details.
5) Client signs a contract with a vendor.

[tesuji]

Are you about to design multi-client software (like SAP R/3)? If so, almost every table must have client's number in the first position of its primary key.

Question: clientid and employeeid are they given by the clients or are they generated on your side?