Database design

Question

Hello,

I am new to database design and I am looking for feedback on a database that I have been asked to build in work. This database is to replace several spreadsheets with the goal of allowing multi-user simultaneous access. 

Database Objective

The objective of this database is to manage our Job records. This has several functions:

• To control who can create a job on our system
• To give each individual job an Unique ID
• Job expenditure - Purchase Orders
• Job Income - Payments received
• The database should also ensure that orders and payments can only be placed against existing Jobs.
• The database should allow us to print purchase orders, if required.
• The database should keep track of our supplier details
• Generate reports showing expenditure and income on each job, and to view all of the jobs that are assigned to a specific project manager.

I have several forms to allow users to enter data to each of the tables. 

The database relationship is outlined below. I would appreciate any feedback that you can give. 

Answer 1

Thanks for this - With job number - its across the multiple fields because I thought it needed to be this in order to record which jobs the orders are against?

Answer 2

The basic model for purchase orders is:

Suppliers----<PurchaseOrders----<PurchaseOrderDetails>----Products

i.e. PurchaseOrderDetails models the binary many-to-many relationship type between PurchaseOrders and products by resolving the relationship type into two one-to-many relationship types.

JobNumber should be a foreign key in PurchaseOrders.  The enforced relationship between Jobs and PurchaseOrders will thus ensure that orders can only be placed against an existing job.

As each supplier will supply a subset of products, there is also a many-to-many relationship type between Suppliers and Products which should be modelled by a ProductSuppliers table in the same way.  It follows from this that PurchaseOrderDetails, in addition to PurchaseOrderID and ProductID foreign key columns, will also require a SupplierID foreign key column.  Consequently ProductID and SupplierID in PurchaseOrderDetails constitute a composite foreign key referencing the composite primary key of ProductSuppliers.  The enforced relationship between ProductSuppliers  and PurchaseOrderDetails on these two columns will thus ensure the integrity of the data, preventing a product being ordered from a supplier who does not supply that product.

Data entry of orders will be by means of an orders form, in which will be embedded an order details subform, in continuous forms view, linked to the parent form on JobNumber.  To print a purchase order is merely a case of designing a report based on a query which joins the Suppliers, PurchaseOrders, PurchaseOrderDetails and Products tables, grouping the report by purchase order, with the order lines in the detail section.

The model for invoices depends on your business model for invoicing jobs.  You will need some means of costing each job, both on the basis of the products purchased in relation to the job, and other costs.

Designing an income and expenditure report per job should not be difficult, but like most things in a relational database it relies on your getting the underlying model right, so that the data can be returned by means of a query used as the reports RecordSource.

A report of jobs per manager requires the jobs table to include a ManagerID foreign key, assuming each job is assigned to one manager only.  This will reference the primary key of an Employees table.

Answer 3

For starters:

• I use a naming convention that avoids spaces. [PO Number] would be PONumber and [Job Number] would be JobNumber.[3-Order Log] would be tblOrderLog.
• I would recommend not using multivalue fields. Most of us veteran developers would use a junction table.
• You have repeating groups of fields with numbers in the [3-Order Log]. This is considered un-normalized (search the web for normalization).
• You have [Job Number] as the primay key in three tables. This is highly unusual since typically there would be tblJobs with a primary key of JobNumber. Any other table could have multiple records with the same JobNumber.
• Supplier address information belongs only in the Supplier table.
• Why do you have Field1 and Field2 in your table?