Project Purchasing Table Design (1 Viewer)

I have a question about table/relationship design. I've attached what I have after many months of google searches, youtube vids and books. The purpose of the database is to track the material orders for each project but I'm not concern about cost only quantities of each item.
At this point I don't know what I don't know. Can anyone tell me if I'm going in the right direction?

The attachment didn't make it. See if this helps:

https://www.access-programmers.co.uk/forums/showthread.php?t=140587

Thanks pbaldy, I tried to upload the pdf file but there was a msg saying I had to have 10 posts before being able to upload files. Is there another way?


Sent from my iPhone using Tapatalk

If you zip it you should be able to attach it. After 10 you won't have to zip.

Material orders per project

Click to expand...

Do you have a parent table for project, child for the orders per project, and child for each item in the order? You may find that each line item needs a child for date/quantity delivered.

Taking a guess as you've not posted what you have yet, but this is fairly standard if you are doing project management and need to keep track of what is where, when, and what is outstanding.

P.S. Welcome to this little outpost of wonderment!

This still isn't working. pbaldy I followed your instructions and still got the same message "I have to have 10 posts before attaching file or links...tried a dropbox link as well...frustrated.

Sorry, I thought zip files were allowed. You can email it to me and I'll attach it here.

<snip>

Here it is. I haven't had a chance to look, dealing with a work problem. If no one else has time, I should be able to get to it later this morning.

Overall it looks good. Here's some things I would address based on what I see from your relationship tool:

Overall--too many tables, I think you can accomplish this with a total of about 8. I address how to get there below. Also, do not use non alpha-numeric characters in table/field names. Underscores are fine, but no ampersands (&) nor slashes (/). It will make life easier when you write queries and code.

Circular Relationship. There should only be 1 way to trace a path between tables in a relationship. There are 2 ways to get from tblProjectData to tblPurchaseOrder--directly and via tblPowerEmployees. One of those relationships needs to go. Or, you just may need a new instance of tblPowerEmployees that isn't connect to both.

Overnomarlization, too many tables -- all those tables with just 1 piece of information (tblOrderSubmitted, tblMaterialCategory, tblOrderSTatus, tblOrderPriority) should go. Instead, you can simply add the possible values to the field that they link to instead.

Too many contact tables -- instead of storing the role of a person in the table name (tblGeneralContractors, tblGCProjectManager, tblPowerEmployees, tblSupplierContacts) make one Contacts table and add a new field called [Role] which holds if they are a GCProjectManager, a GeneralContractor, a Supplier Contact, etc.

NOTE: I was writing this while plog was posting...



GENERAL NOTES
I would not use "&" or other special characters in table names.
I would never use just "ID" in the tables. I would use ID followed by the table name for the autonumber field that is your primary index. This avoids issues later when you are trying to make sure you are linking based on the tables ID field.

ProjectData is linked to PowerEmployees by specific jobs.

For myself, I would have a linking table that hold the ProjectDataID, EmployeeID, and the job position. Optionally you could include start and end date to track who has had that position in the past. This allows you to add positions as needed without changing your table structure. It also makes reporting easier.

NOTE: Same may or may note be true for PurchaseOrder. Based on your business model can more than one employee be related to a given PO?

For ProjectStatus, Delivery Method, Oder Priority, Material Category, OrderSubmittedVia, and Order Status, I would have ONE lookup table

How I normally set up a unified lookup table is
LookupID AutoNumber
LookupType String (For you this would be delivery, status, ect..)
LookupValue String (Value displayed, fulfilled, ordered, ect..)
LookupDescription String(Optional - Explination for the value, if needed)
LookupSeq Number(Optional - for programmer or administrator definied sequence of a lookup)

I am guessing the tables with just an ID field in them are ones you are working on.

You may have noticed that you have several tables with effectively the same structure; first name, last name, phone. I would have one table for "Contacts" that has this information. This means you could have a contractor who is also a supplier without having to enter their information twice.

The one thing I can see missing is the child under ProductsOrderLines. If you order 900 yards of concrete odds are it won't all show up the same day. You should have a child table to record when materials are delivered for a line item. This way you can record that 35 yards was delivered on March 15th, 70 on march 16th, and 120 on March 20th. You could then run through these child records to see how much is outstanding, and you can entere a "Cancelled" child for any amount that needs to be cancelled for any reason.

These are just items I can note from basic data structures and my experience with project management. As you expand this I'm sure you'll have a LOT more questions.

I do hope this help!

P.S. as this is based on my experience I am fully expecting many more comments based on other's background. Each will have a different perspective and reason for their way of doing things. No one person will be right. Please feel free to pick and choose what will work for your specific needs!

Thank you plog and Mark. Ampersands have already been removed. I will start the redesign based on your other suggestions.
In regards to removin tblMaterialCategory, I would like to be able to sort the materials for each project by category and my material list is huge. Can i still do that with a lookup values, in a field?

Hi

You would normally have 2 tables to deal with this scenario.

One table listing Categories and then another table storing all Items associated with Each Category.

You would then use what is known as Cascading Combobox's onyour Data Entry Form.

The 1st Combobox allows you to select a Category then a Second Combobox would oly display those Materials associated with the Category Selected

Depends a bit on how "Huge" your list is.
You can extend the basic lookup table by having sub-lookup values.

When I use it, LookupType is normally dictated by which control will be using it. This allows you to create a "Parent" combobox that identifies which sub-type you would want to use. Look for cascading comboboxes as an example.

You could then have a set of records for LookupType "MaterialCategory" that allows you to select subcategories. Those subcategories then drive the actual lookup of a material.

Sample records could be;

LookupID: 1
LookupType: MaterialCategory
LookupValue: Stone

LookupID: 2
LookupType: MaterialCategory
LookupValue: Pipe

LookupID: 3
LookupType: MaterialCategory
LookupValue: Wire

LookupID: 4
LookupType: MaterialCategory
LookupValue: Brick

LookupID: 5
LookupType: MaterialCategory
LookupValue: Wood

LookupID: 6
LookupType: Stone
LookupValue: Granite

LookupID: 7
LookupType: Stone
LookupValue: Marble

LookupID: 8
LookupType: Stone
LookupValue: Limestone

You would then have a combobox that is limited to [LookupType] = "MaterialCategory" to select the type of material. You would then have a child where [LookupType] = '" & Me.ParentLookup & "'".

This allows you to reference the same table multiple times, if needed, to get down to the actual value you may be selecting.

I normally have a parent of "LookupParents" or the such to allow me to enter top level lookup values that drive these. The first place I started using this allowed me to get rid of 20ish tables with very few records for one table with several dozen records.

Thank you Mike, i will keep the tblMaterialCategory and use the cascading combo box.

Mark, your post gave me some ideas. I will probably have about over 1000 material items. they will be in categories of board, steel, insulation etc. then there could be sub-categories of 1/2", 6"...as i'm typing this I'm thinking of other subs.

This is awesome!!

Please remember, both Mike and I have approaches that WILL work. The question for you now is "Which fits your business model/needs better".

Part of what you get to do now is to look through all of your materials and see what else will need to be associated with a given item. Do you consider it the same item if you have multiple suppliers for the same material? i.e. do you consider it a different item if a 6" board of length X comes from supplier A instead of supplier B? This may drive linking items to specific suppliers. If you do not, this may be a piece in your PO system instead.

Thanks Mark, the item doesn’t change whether it’s from supplier A or B. The manufacturer may be different. To keep things simpler I’ll leave the manufacturer out. And each order will have only one supplier.


Sent from my iPhone using Tapatalk