Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-08-2019, 05:56 AM   #1
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Table Design & Relationships

I was provided a database (with inadequate limited data already dumped in by a predecessor) and told to make it work. The end goal is to include all product lines and their component details at the time of shipment to a customer. Currently, the database includes one table with 1200 records. Each record contains multiple fields of data and appears to have originated from an Excel spreadsheet.

In most cases, each SHIPMENT would include multiple records. Each record would capture the details of each component shipped in a single system (product line). The product line includes multiple component part numbers that stay the same. Each component part needs to capture the following variable data: serial number, revision, software info, notes, etc. The customer and tracking info is duplicated for each line.

I have the following chunks of data I need to work with:
A customer may have multiple Purchase orders.
A purchase order may have multiple delivery addresses.
A purchase order may have one or multiple product lines.
A shipment may have one or more systems containing multiple serialized components, -and/or- individual components going to one address.
Each product line will have multiple serialized components and other info (revision, software version, etc.) to track.
Each component will have an independent serial number (and/or other info to track).
Each shipment will have a top level system serial number (and/or other info to track).
A product line (or component) can be shipped to multiple customers (or ordered from multiple purchase orders.

My initial thoughts were to have each product line as a separate table since the components stay the same and only a few fields for each component (serial number, revision, software version, notes, etc.) might vary from shipment to shipment. If I know how to format one product line (such as the 60cm dish in the provided sample) I am sure adding another product line will be fairly straight forward.
Other tables I was considering include one to capture the tracking info, ship date, contract shipped under, and destination. A Customer table is probably warranted (address, phone, contract/PO number, notes, etc.). Right now I am just trying to keep data capture going (record updates) as I figure all this out.

A copy of the database in question is attached.

This is my second database project. (The previous project was a few years back when in college. Thus this is a real struggle to pull this together. Any suggestions on table layout are very welcome.

Thank you in advance for your help.
Attached Files
File Type: zip Shipment Warranty-v.4.2.zip (272.4 KB, 7 views)

PuzzledNH is offline   Reply With Quote
Old 11-08-2019, 09:29 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,369
Thanks: 0
Thanked 742 Times in 727 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Table Design & Relationships

you can breakup the 1 table into its relative components.
run some make table queries from the source table to create the new tables and atomic values:

POs,
Addresses, shipto, mailto
prod line,
etc.
Ranman256 is offline   Reply With Quote
Old 11-08-2019, 12:21 PM   #3
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by Ranman256 View Post
you can breakup the 1 table into its relative components. run some make table queries from the source table to create the new tables and atomic values:
Hi Ranman256,
You completely lost me. What are make table queries and atomic values?

PuzzledNH is offline   Reply With Quote
Old 11-08-2019, 12:37 PM   #4
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,996
Thanks: 114
Thanked 3,011 Times in 2,738 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Table Design & Relationships

In case he doesn't respond, I think Rahman was suggesting making several new tables to hold each 'section' of the underlying data.

Here's another suggestion with the same aim.
Use the Analyse Table feature in Database Tools. It will recommend ways of structuring your data better to remove repetition. If you follow the advice, the end result should be 'normalised' data which is how it should be structured. Same idea but Access will do the work for you. If that doesn't mean anything to you, I suggest you do an online search and read about it.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


"As we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know. But there are also unknown unknowns the ones we don't know we don't know. It is the latter category that tend to be the difficult ones" Donald Rumsfeld
isladogs is offline   Reply With Quote
Old 11-08-2019, 08:38 PM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Table Design & Relationships

I can't make any sense out of table1. Looks like everything to the right of email has a data value as the column name and the values are all empty so I have no idea what they are for.

You started out with a description of the types of data you have. Now try to map the columns to each of the entities you defined.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-12-2019, 05:32 AM   #6
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by Pat Hartman View Post
I can't make any sense out of table1. Looks like everything to the right of email has a data value as the column name and the values are all empty so I have no idea what they are for.

You started out with a description of the types of data you have. Now try to map the columns to each of the entities you defined.
Field labels to the right of Customer Email are part number designations for individual components.

I.e.: 60cm-140087-MO
is looking for the manufacturing order (-MO) we used to build a 6 pin configured connector (140087) for the 60cm product line (60cm).

The 60cm- would be a specific product line. The database I provided was only configured (or set up) to capture one product line (and a few components). My predecessor had just started data capture and had not used these fields yet. The database will have multiple product lines. Each product line will have it's own starting designation, set of component part numbers, and similar data to capture.

The 6 digit number 140087 is the component part number.

Characters after the (-) dash indicate the data type being collected for this part number.
-MO is a manufacturing Order
-Rev is the part revision or software version
-Contract is is to capture contract data from a bar code scan.
-3D code is to capture data from a bar code scan.
-SN is the component serial number.

Those items with a yes/no data type would be checked off to indicate the item was shipped with the system.

Thus a full 60cm system may (or may not) require a data capture for each 60cm part number if the component is shipped as part of the system.

Hopefully this makes sense.

I am finding that as I answer these questions I am readjusting how I think the data structure should be. So thank you for all the prodding.

I have most of the data fields for a second product line defined and entered in Table1. (No data, just field names and data types.) I figure that once I can normalize the data, the effort would just move into a different table. (or so I hope.) I will try to map the entity types to the columns and post in the next day or so. Thank you.

Last edited by PuzzledNH; 11-12-2019 at 05:52 AM.
PuzzledNH is offline   Reply With Quote
Old 11-14-2019, 01:38 PM   #7
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Sorry for being silent on this the past couple days. I played around a bit to try and clarify Product Lines. I now have 2 tables (one for each of two defined product line) to show the data that would need to be captured when that product is shipped.
Table-60cm_Data
Table-PRS11_Data

I entered a "Description" for many of the fields in the table design view. Each of the two tables also includes a small portion of data. Table 1 still exists, with only the data that has not yet been assigned to a product line. I.e. I am trying to determine if I am on the right track with the two new tables and if they need to be broken down further or if I am heading the wrong direction and need to rethink the structure.

I also attached a two page PDF to try and depict what I am trying to transition this mish-mash of data to actually do.

Hopefully this makes some sense.

Again, thanks for any assistance in laying out my data relationships.
Attached Files
File Type: zip Shipment Warranty-v.4.3.3.zip (989.7 KB, 13 views)

PuzzledNH is offline   Reply With Quote
Old 11-14-2019, 09:29 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Table Design & Relationships

You need to work more on the normalization.

All product likes will end up in a single table. You would NOT have a table for each product line. Pull out all the common attributes and put them in a table named tblProductLine.

The part information goes into tblParts.

Then you need a junction table that you might call tblProductParts. It relates parts to a product that they are used on. Some parts will be used for only one product but other parts will be used for multiple products. So all those columns to the right of the common columns become rows in the product table where their descriptions and other attributes exist. There isn't enough detail for me to ascertain with certainty that parts are used on multiple products but given all the manufacturing systems I have developed, it would be totally surprised if there was no overlap.

Your model is somewhat complicated by having all the parts serialized. You can create a model product but at some point, the actual serialized part needs to be assigned to the product being bulit for clientX and I'm not sure the best way to do that without a lot more information. For example, you can write code that using the model to decide what parts are required, pulls a specific part serial number and assigns it to the product being built. Doing that would essentially remove that specific part from inventory.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-15-2019, 06:32 AM   #9
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by Pat Hartman View Post
There isn't enough detail for me to ascertain with certainty that parts are used on multiple products but given all the manufacturing systems I have developed, it would be totally surprised if there was no overlap.
Each product line has a completely different set of components. The components may look similar, However the component part number will vary from product line to product line. I.e. There are no component part numbers that are used on more than one product line. Additionally, a product line may have a choice between two or more similar components (customer can choose the blue one or the red one but not both). When looking up the records for warranty and/or customer support needs, we would already know what product line to run a query on. The product line is defined by a top level part number which is assigned a system serial number at time of shipment. This part number defines a family tree (or BOM). The family tree defines all the components of the system. The database is capturing the "as shipped" information of the system serial number (including each components serial number, revision, and other details scanned from bar code labels).

In some cases, system components will go obsolete and replaced by new part numbers that may require capture of new criteria. Trying to locate the updated components associated to a system in a large table (when updating reports, queries, forms, etc) seemed like it might become painful. (Just a perception.)

This is why my first thought was to have a separate table for each product line. If all the components were captured in one table, the table might have a couple of hundred columns. As the data gets populated I was not sure if performance would be impacted using one table vs multiple tables.

My next step is to separate the "customer" and "shipping" information from everything else. This would include contractual information, shipping addresses, delivery locations, tracking numbers, shipment dates, what shipped on what date, etc. I am thinking this should be two tables (one for customer & contract, and one for shipment)?

Having said this, does it still make sense to keep all the component parts on one table? I am trying to educate myself if there are any positives or negatives in this layout.

Again, I sincerely appreciate the help and input. Thank you.
PuzzledNH is offline   Reply With Quote
Old 11-15-2019, 07:44 AM   #10
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,398
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design & Relationships

In general; someone who has no idea what your organization does, should be able to look at your tables and have a rough idea what each field is for. [PRS11-900204-UID] means nearly nothing to me. I say "nearly" because the UID part is generic enough that I know it probably represents some sort of ID. That means the "PRS11-900204" portion is actually data and should itself be a value in a field, and not part of any field name.

You've made that mistake a lot. Further, even without knowing what the data is I see you have sets of fields (e.g. [PRS11-...-SN], [PRS11-...-Contract], [PRS11...-MFR])) . When you start doing that, its time for a new table to hold those sets of data. So you would strip those fields out and put them in a new table like so:

tblInsertGoodTableNameHere
igtnh_ID, autonumber, primary key of table
ID_Data, number, foreign key back to table you are taking the fields out of
igtnh_PRS, text, this will hold the PRS data in the field name (e.g. PRS11)
igtnh_Code, text, this will hold the 6 digits after the PRS data (e.g. 930424)
igtnh_Type, text, this will hold the suffix at the end of the current field name (e.g. SN, UID, MFR, etc.)
igtnh_Value, text, this will hold the actual value in each of those fields

That's it. So instead of all those sets of values you would have 1 record in this new table for those values.

You've made that mistake a lot and you resolve it in the same manner--with a new table for that data. I believe that one table could accomodate a ton of the data you have.

Lastly, only use alphanumeric characters and underscores in table/field names. All those hyphens and slashes are going to make coding and querying more difficult later on.
plog is offline   Reply With Quote
Old 11-15-2019, 09:00 AM   #11
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by plog View Post
In general; someone who has no idea what your organization does, should be able to look at your tables and have a rough idea what each field is for. [PRS11-900204-UID] means nearly nothing to me. I say "nearly" because the UID part is generic enough that I know it probably represents some sort of ID. That means the "PRS11-900204" portion is actually data and should itself be a value in a field, and not part of any field name.

You've made that mistake a lot. Further, even without knowing what the data is I see you have sets of fields (e.g. [PRS11-...-SN], [PRS11-...-Contract], [PRS11...-MFR])) . When you start doing that, its time for a new table to hold those sets of data. So you would strip those fields out and put them in a new table like so:
Lastly, only use alphanumeric characters and underscores in table/field names. All those hyphens and slashes are going to make coding and querying more difficult later on.
I think I am missing something.
Each of these would be a field name to capture data, not data itself. The first section of the field namePRS11- or 60cm- is just to help me differentiate which product line that field is associated to. This would actually be dropped from the field name once I have the tables set up properly. The numerical part of the field name 900204- refers to a specific component. Some components may have more than one bar code to scan and each scan is delineated by the characters after the second dash. So if you see PRS11-900204- more than once, each instance is capturing a different data set for a component.
PRS11-900204-UID (Captures an Identification Number)
PRS11-900204-SN (Captures a Serial Number)
PRS11-900204-Rev (Captures the item revision)

If I have two fields "PRS11-900204-SN and PRS11-900225-SN" that end in SN (or other designation), they are two completely different parts that require capture of that component's serial number (or other data). Thus, a system shipment will require capturing multiple serial numbers from different components.

Thanks for any input that can be provided.
PuzzledNH is offline   Reply With Quote
Old 11-15-2019, 09:12 AM   #12
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by plog View Post
Lastly, only use alphanumeric characters and underscores in table/field names. All those hyphens and slashes are going to make coding and querying more difficult later on.
I am trying to fix this now. Spaces will be replaced with underscores (or completely deleted). Some fields will be renamed to shorten them and to align with the specific data being captured. I am also verifying that field types and sizes make sense for he captured data.

Again, My initial goal is to get the table layout to make sense. This was a hand-me-down from a predecessor and I am doing this as a side function to my actual job.

Thanks again.
PuzzledNH is offline   Reply With Quote
Old 11-15-2019, 09:52 AM   #13
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,398
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design & Relationships

Quote:
The first section of the field namePRS11- or 60cm- is just to help me differentiate which product line that field is associated to
You agreed with me there-- "PRS11-..." is data. In a normalized table structure data goes in as values in fields, not as part of field names. Since you need to differentiate between products you do so in the data. You would accomodate this new data by adding a field to hold the product data, not by putting it in a name.

In a sales table you would record sales by Year like this:

SalesPerson, SalesAmount, SalesYear
3, 151, 2019
3, 202, 2018
3, 351, 2017

Not like this:

SalesPerson, Sales2019, Sales2018, Sales2017
3, 151, 202, 351

The year is data, therefore it gets its own field, not crammed in the field name.


Further, each discrete piece of data gets its own field. If "PRS-123456-UID" represents 3 pieces of data, you accomodate all that data with 3 different fields.
plog is offline   Reply With Quote
Old 11-15-2019, 11:08 AM   #14
PuzzledNH
Newly Registered User
 
Join Date: Nov 2019
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
PuzzledNH is on a distinguished road
Re: Table Design & Relationships

Quote:
Originally Posted by plog View Post
You agreed with me there-- "PRS11-..." is data. In a normalized table structure data goes in as values in fields, not as part of field names. Since you need to differentiate between products you do so in the data. You would accomodate this new data by adding a field to hold the product data, not by putting it in a name.

In a sales table you would record sales by Year like this:

SalesPerson, SalesAmount, SalesYear
3, 151, 2019
3, 202, 2018
3, 351, 2017

Not like this:

SalesPerson, Sales2019, Sales2018, Sales2017
3, 151, 202, 351

The year is data, therefore it gets its own field, not crammed in the field name.


Further, each discrete piece of data gets its own field. If "PRS-123456-UID" represents 3 pieces of data, you accomodate all that data with 3 different fields.

PRS11-123456-UID actually represents only one data point, not three.

To accommodate three different 3 data points for the same part, the characters at the end of the field change.
PRS11-123456-UID
PRS11-123456-SN
PRS11-123456-MFG

If I were to use just UID, there could be 15 different parts (I.e. 6 digit part numbers) in a system that have a UID number. By including the 6 digit number before UID I now identify which specific part the scan is associated to. The UID for 123456 may be an alpha numeric 50 character field and the UID for 987654 may be a numeric only 5 character field that does not allow for duplicates.

PRS11 (as it stands in the database today) is not data. It is a temporary beginning of the filename (or a quick reference) to let me know what product line that field is associated to. The actual filename (once I have all the different product line fields defined) will delete PRS11- and only retain the six digits and ending characters (I.e. 123456-UID) as the filename.

So the 60cm product line consists of approx. 20 components and 25 points of data to track. PRS11 product line consists of 40 components and 75 points of data to track. Placing 60cm (or PRS11) in front of the filename allows for a quick check that I addressed all data points for this product line without having to look up if the field name belongs to which product.

Hopefully this makes sense.
PuzzledNH is offline   Reply With Quote
Old 11-15-2019, 12:07 PM   #15
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,398
Thanks: 11
Thanked 2,282 Times in 2,234 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Table Design & Relationships

Quote:
PRS11-123456-UID actually represents only one data point, not three.
Semantics is getting in the way, your just not thinking general enough. When you put a value in the field [PRS11-123456-UID] that is one data point, but it represents 4 pieces of data: PRS11 is data, 123456 is data, UID is data and the value you've placed in the field is data. That is wrong. Those 3 pieces of data should not be in the field name, they too should be values in a field. See my sales by year example.

Quote:
So the 60cm product line consists of approx. 20 components and 25 points of data to track.
This data should be accomodated in another table, and the amount of components and points of data is irrelevant in the structure of your database.

tblComponents
comp_ID, autonumber, primary key
ID_ProductLine, number, foreign key to Product line table
comp_Type, string, type of component--it sounds like you want to jam this in a field name
comp_Value, string, this is holds the value you are putting in your table currently

That's it, those field accomodate an unlimited amount of components and product lines. Instead of 20 fields for 20 components, you insert 20 records into this table for them.

plog is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Could use some help on table (relationships) design 88scythe Tables 6 12-18-2012 11:49 PM
Table Design/Relationships Dartos Tables 8 09-12-2010 04:47 PM
Table Relationships and design bowks14 Tables 3 02-20-2009 01:52 AM
table design and relationships... snowman Tables 12 03-25-2006 07:56 PM
Help with Relationships and table design: Bill Harrison Tables 7 01-12-2005 06:44 AM




All times are GMT -8. The time now is 01:17 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World