Table Design & Relationships (1 Viewer)

PuzzledNH

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
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.
 

Attachments

  • Shipment Warranty-v.4.2.zip
    272.4 KB · Views: 495

Ranman256

Well-known member
Local time
Today, 19:03
Joined
Apr 9, 2015
Messages
4,339
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.
 

PuzzledNH

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
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?
 

isladogs

MVP / VIP
Local time
Today, 23:03
Joined
Jan 14, 2017
Messages
18,186
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:03
Joined
Feb 19, 2002
Messages
42,976
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.
 

PuzzledNH

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
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:

PuzzledNH

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
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.
 

Attachments

  • Shipment Warranty-v.4.3.3.zip
    989.7 KB · Views: 522

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:03
Joined
Feb 19, 2002
Messages
42,976
You need to work more on the normalization.

All product lines 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.
 
Last edited:

PuzzledNH

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
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.
 

plog

Banishment Pending
Local time
Today, 18:03
Joined
May 11, 2011
Messages
11,612
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.
 

PuzzledNH

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
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

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
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.
 

plog

Banishment Pending
Local time
Today, 18:03
Joined
May 11, 2011
Messages
11,612
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.
 

PuzzledNH

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
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.
 

plog

Banishment Pending
Local time
Today, 18:03
Joined
May 11, 2011
Messages
11,612
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.

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

Banishment Pending
Local time
Today, 18:03
Joined
May 11, 2011
Messages
11,612
Also,

PRS11 (as it stands in the database today) is not data...PRS11 product line consists...

Your so close to the data, you've lost sight of what actually is data. PRS11 represents a product line, therfore it is data. Data goes in as values in fields, not in field names. It goes into a field that describes in general what that data is a specific example of. In this case you should have a field named: [ProductLine]
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:03
Joined
Feb 19, 2002
Messages
42,976
If all the components were captured in one table, the table might have a couple of hundred columns.
You are really struggling with the concept of data being rows rather than columns. There is no reason in the world that I would ever need to know all the names of all the components of all the products in order to define the schema. Let's abstract this a little. If you were defining a database for a school would you create a separate table for each family and name the columns based on the names of the children in the families so Familyx has columns named Sue, John, Joe and FamilyY has columns named Mary, Susie, Jimmy, Al, Hal, David, and 12 more? This essentially what you are doing.

Does Boeing keep a separate table for each model plane? Does Pratt&Whitney keep a separate table for each model engine? Does Clairol keep a separate table for each product line so shampoo is one table, Hair dye is another, Conditioner a third and then we get into the cleaning products. The answer in all cases is no. Boeing only makes planes and Pratt only makes engines but Clairol makes a multitude of different personal and home care products with completely different formulations and component lists. Yet they all use a single product table.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:03
Joined
Feb 28, 2001
Messages
27,001
One of the most important skills that you need to learn is alternation of scope. There will be times when you need to get into the weeds. There will be time that you have to look at the overgrown field as a single thing. Right now, you are focusing on the weeds and not recognizing the overgrown field.

When you make tables for the purposes you have described, you are building a business model of some sort. The idea is to build abstractions so that you can treat them all in the same way at some level. You put together those things that are treated similarly. Maybe the detailed contents are different, but you probably treat each product line in the same way at the high-altitude viewpoint. And that is where all of those products you are naming become simple data records in a single table that doesn't contain a serial number or part number as a table name OR as a field name.

As my managers often did for me, I'm suggesting that you try to step away from where you are standing and look at the big picture. Not trying to be a downer here, but if you cannot step away and see the problem in overview, you will not be able to make this project work in a reasonable time frame because you will rapidly become overwhelmed with an intractable number of tables.
 

PuzzledNH

Registered User.
Local time
Today, 19:03
Joined
Nov 4, 2019
Messages
36
Your so close to the data, you've lost sight of what actually is data.

You are really struggling with the concept of data being rows rather than columns.

Right now, you are focusing on the weeds and not recognizing the overgrown field.

OK, I get the hint. :)
I am struggling with what is data vs what should be considered field names. And I thank everyone for pointing me in the right direction.

However, If I am looking at the following fields for the table:

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​


...I am also struggling with how I present the fields to the person who will be entering the data so I am collecting the correct system configuration for the shipment. As an example, I have a form "Form-60cm Dish" that captures scanned data and inputs it in a specific field.
I.e. Item 4 Scan1 enters scanned serial number data into the field for 60cm-900338-SN.
Item 4 Scan2-Contract enters scanned contract number data into the field for 60cm-900338-Contract.


I already know the table structure needs to be laid out before any form is created. This was set up as a sample to show the specific info the 60cm product line must capture based on a documented inspection instruction that tells the person how and what to capture. So no need to beat me up on this. ;).

My struggle comes from how do I associate the scanned serial number to the specific part number being scanned?

If I have this correct, the scanned data would be collected in the tblComponents table. Each scan would go into a new line
comp_ID, autonumber, primary key
which is the primary key for this table. This part makes sense to me.

I would have a field for each data type to be collected.
comp_Value, string, this is holds the value you are putting in your table currently

I.e. In the case of Item 4 Scan1 I would have a field called SN (Short Text). Item 4 Scan2_contract would be a field called contract(Short Text). Item 1 900339-MO would be a field called MO (Short Text)
Since I have multiple parts that collect SN, Contract, MO, etc. I would need some way to associate the scanned data to the component part number. Is it correct to assume the part number is in the ProductLine table?

Now we have a second table that comes into the picture:
ID_ProductLine,
This is a number field (long integer?) that directly links as a foreign key to a Product line tables primary key.
This is where my struggle starts. Is this one table per product line similar to the Table-60cm_pn_lookup? Or will this be one table that includes all product lines? Would the primary key be the unique part number or should this be an autonumber field with part number as a seperate field name?


comp_Type, string, type of component--it sounds like you want to jam this in a field name
I would think the type of component (or component name) to be included in the Product line lookup table. A Product line consists of multiple part numbers. Each part number has a part name.
Think of a product line as a full computer system with a part number for individual components.
1234=CPU
2345=monitor
4567=keyboard
6789=hard drive,
9876= installed software version,
etc.
 

mike60smart

Registered User.
Local time
Today, 23:03
Joined
Aug 6, 2017
Messages
1,899
Hi

In your table "PRS-11-Data" you have the following fields together with many more:-

Fields.PNG

So when you create your Form for Data Input what value would you Input for each of the following fields:-

PRS11-930424-SN
PRS11-930424-Contract
PRS11-930424-MFR
PRS11-930424-UID
PRS11-930424-Part
PRS11-930424-3D_Code
 

Users who are viewing this thread

Top Bottom