Help with simple design

[Davebert]

Hi everybody. I am currently using LibreOffice Base. I have ordered a new retail version of MS Office Pro 2010 so I will have ACCESS again when it arrives. I am somewhat a noob with databases but have some experience making flat file databases in the past and just recently again. I tried to learn MS Access years ago and read one of the 700 page books to learn it, but I always got confused by the relationships of the table so I just ended up using flat file single table databases. Now 20 years or more later, I find myself in the same situation where I want to figure out how to make a multi-table database and again I am confused. I am probably making a simple mistake so I may not need a lot of help. Here is what I have so far. I am making a database for personal use that holds all the data for 3D Printer filaments. In the table I have the following fields:

ID Integer Autonumber
TYPE VarChar
COLOR VarChar
BRAND VarChar
TD Integer
HEX VALUE VarChar
QUANTITY Integer
NOTES VarChar

I populated all the info that I had and filled 130 rows with info that is currently available so ID, TYPE, COLOR, BRAND, QUANTITY and NOTES, but the TD and HEX VALUE are empty until I finish.

So after I did that I converted the db to a spreadsheet and then converted them back to separate tables with one field each. When I use the tool to make relationships besides running into numerous error messages, I ended up with all 1 to 1 relationships when I really think I want many to many. Seems I have a little trouble with the foreign key stuff.

Questions: I think I might only need 2 tables but am not sure which items for table one and table two. I am thinking that I always have to enter the brand, type color and quantity so that would be table one and the rest in table two. Does that sound correct. I just need a few pointers on the design of this and also the foreign key stuff but I am sure I can do the foreign key easy enough.
Thanks

 

[plog]

I am making a database for personal use that holds all the data for 3D Printer filaments

That's the only sentence you gave us that gives us insight into what your data is for. So, going off that and seeing the fields of the one table you posted--I think you only need one table--the one you have, exactly like it is.

What do you think would be in a second table? Also, why would TD and HEX values be empty? Where does their information come from?

 

[XPS35]

that holds all the data for 3D Printer filaments.

I think you should first explain more about the purpose of the database. What you mention here doesn't mean much.
Because I also see a quantity field, it could be a stock administration. This would mean that you record stock changes in one or more separate tables. Simply manually changing the stock in the items (filaments) table is an unreliable method. For example, you create a table for receipts that is related to the items table.
You can also create separate tables for the permitted values of colors and brands and relate them to the items table.

I have no idea what TD and HEX stand for.

 

[MajP]

Without knowing more about what you want to do in a business sense and your processes it gets kind of hard.

Take for example Notes.
It may be fine for what you need to keep a single text fields for one note and additional data about a note is not needed. So that design if fine

But maybe you need to know when the note was added, who added it, the type of note, etc. And you need to track each note sperately. Then you might need a related table where you could have many notes.

tblNotes
-- NoteID
--NoteDate
--NoteEntryID_FK (relates back to your employee table telling who entered the note)
--NoteType (Information, Action, Resolution)
--FilamentID_FK (relates back to your main table)

 

[Davebert]

That's the only sentence you gave us that gives us insight into what your data is for. So, going off that and seeing the fields of the one table you posted--I think you only need one table--the one you have, exactly like it is.

What do you think would be in a second table? Also, why would TD and HEX values be empty? Where does their information come from?

The TD is the "transmission distance" which is not needed for everything I can do with a 3D printer and it takes some time to generate that info through various methods, The HEX is just the hexadecimal value for a color and is also only used here and there but can be useful sometimes. I was thinking with a relational database I could more easily search for certain TYPES (PLA, PLA+, PETG, ASA, ABS. NYLON etc) in certain colors. Or when I get the database more complete I could search all one color or many colors by the TD value.

 

[Davebert]

I think you should first explain more about the purpose of the database. What you mention here doesn't mean much.
Because I also see a quantity field, it could be a stock administration. This would mean that you record stock changes in one or more separate tables. Simply manually changing the stock in the items (filaments) table is an unreliable method. For example, you create a table for receipts that is related to the items table.
You can also create separate tables for the permitted values of colors and brands and relate them to the items table.

I have no idea what TD and HEX stand for.

I have a lot of 3D printer filaments and I will be buying a lot more. I may have quantity of 6 white PLA filaments that are one brand or a bunch in other brands and types and same for a whole lot of other color and types. When I use a spool of a filament I can easily manually adjust the quantity and I will likely never delete any records as far as a row goes. Mainly I would like to be able to do a query and look up for example RED, PLA, QTY and later TD.

TD is Transmission Distance and that is basically the amount of transparency a filament has. It is used in making 3D printed images that use multiple colors layered over each other from low TD color values to high TD color values. So Black would have a low value as you can't see through it where white is a high TD value. That said, there are many white colors out there, or any color has a range of TD values and it is important. !0 different whites can have 10 different TD values based on their transparency.

HEX is just the hexadecimal value of a color. #FFFFFF = white while black is #000000

 

[Davebert]

Without knowing more about what you want to do in a business sense and your processes it gets kind of hard.

Take for example Notes.
It may be fine for what you need to keep a single text fields for one note and additional data about a note is not needed. So that design if fine

But maybe you need to know when the note was added, who added it, the type of note, etc. And you need to track each note sperately. Then you might need a related table where you could have many notes.

tblNotes
-- NoteID
--NoteDate
--NoteEntryID_FK (relates back to your employee table telling who entered the note)
--NoteType (Information, Action, Resolution)
--FilamentID_FK (relates back to your main table)

This is just a personal database for my use only. No other people will ever access it. It will grow with many more filaments along with their related data for use in inventory and info that I would like to be able to search a little more accurately and to the point. Like if I want to see if I have any green PETG, and what the TD values are so I can choose the best one for my use in making 3D images from a photograph using a program called HueForge, that allows me to manipulate printer filaments and stack them in order to make a cool piece of finished art. Like this:

 

[June7]

You would have one table for records of each filament specifications (color, brand, etc). You could have "lookup" tables to help with creation of new filament records. For example, if you want to limit/standardize color choices, a table would have those values and a combobox (or listbox) on form would pull a list from that table for user to select from. Here you have to make a data design choice - save color ID or color text. If you save ID then when you build a report its dataset will have to be a query that joins these tables so the color text can be retrieved (tables are related by primary and foreign key fields, hence the designation "relational database"). If you save text then no join is required but saving text takes up more storage (as well as has other issues I won't get into). Could have a lookup table for brands as well. Your filament table is then a "junction" table for a many-to-many relationship - each color can associate with multiple brands and each brand can associate with multiple colors. Then I suppose type can also have a lookup.

If combinations of these 3 fields should be unique, then I suggest you set them as a compound unique index (not a compound key). If they do not have to be a unique combinations and instead constitute a "parent" (or master) for multiple combinations with other data, yes another table where they are unique combinations might be called for and other data is saved in "child" table (this would be a 1-to-many relationship). I would not save these 3 fields as a compound foreign key into child table, but instead save record ID.

It is a balancing act between normalization and ease of data entry/output. Search on the phrase "normalize until it hurts, denormalize until it works".

Why did you go with Access 2010 and not a later version? I just this last year upgraded from 2010 to 2021.

 

[Davebert]

You would have one table for records of each filament specifications (color, brand, etc). You could have "lookup" tables to help with creation of new filament records. For example, if you want to limit/standardize color choices, a table would have those values and a combobox on form would pull a list from that table for user to select from. Here you have to make a data design choice - have combobox save color ID or color text. If you save ID then when you build a report its dataset will have to be a query that joins these tables so the color text can be retrieved (tables are related by primary and foreign key fields, hence the designation "relational database". If you save the text then no join is required but saving text takes up more storage. Could have a lookup table for brands as well. Your filament table is then a "junction" table for a many-to-many relationship - each color can associate with multiple brands and each brand can associate with multiple colors.

Why did you go with Access 2010 and not a later version? I just this last year upgraded from 2010 to 2021.

I think you know what I am trying to do. I was just trying to figure out exactly which fields would be in the first table and which ones would be in the second or even third table. Maybe I need to learn how to do the queries and forms too. With my simple database I didn't feel like I could search as accurately as I wanted to. Like search by TD value or by color or by brand etc.

I got the Access version 2010 because it was inexpensive and I had read online about some people having issues converting or importing older Access database files to modify them and people responded that if you had the Access 2007 or newer it wasn't an issue. I guess Access had a major update to the engine or something. Plus I am just a guy with a bunch of interests and hobbies so it's all for personal use.

 

[plog]

Still see only 1 table, exactly as it is now.

 

[June7]

I did more edits on my previous post while you were reading. Might review again.

I don't know why search with your table would be less accurate.

Access 2021 can still open 2003 MDB files, for older files Access 2010 should be helpful.

I found a source that offered 2021 for only $20.

 

[Davebert]

Still see only 1 table, exactly as it is now.

Right. My question is what fields should I put into table 1 and table 2 for example? Or even table 3 if it makes any sense,

 

[Davebert]

I did more edits on my previous post while you were reading. Might review again.

I don't know why search with your table would be less accurate.

Access 2021 can still open 2003 MDB files, for older files Access 2010 should be helpful.

I found a source that offered 2021 for only $20.

I need that source I paid $45 for the whole Office Pro Suite. I will review your edited post. Thanks. I just read your edited post and that helps a lot to know I can do what I want and a couple of ways to accomplish it. I will go with the first suggestion to make it easier on my brain for now. As far as searching my already made table would not be less accurate might be because I don't know how to search it properly, at least with LibreOffice Base.

 

[plog]

No it doesn't make sense. 1 table makes sense. With what you have given us, I see only 1 table, exactly as it is. Again, you only need one table for this data, exactly as you have it now.

 

[MajP]

@Davebert,
You do not divide into multiple tables just to have multiple tables. You can have 100 columns and if every column uniquely describes that record than it is what is called normal. You create related tables for reasons.

Things where you need multiple tables.
1. Repeated information. For example if you had a lot of information about your "Brand" such as
Address, City, State, POC, Phone Number,....
you do want to repeat this every time in Your data table.
You enter that once in the Brand table.
TblBrand
--BrandID
--Address
--City
--State
--Phone
--POC

In your data table you only hold a Foreign key (brand id). So instead of your current Brand you would have something like BrandID_FK. Which holds the key to BrandID in tblBrand

2. Related Records.
As I said if you wanted to track many notes for your data then you could have a "Child table"
This is often obvious where the table will have something like
Note1 Note1Date Note 2 Note2Date Note3 Note3Date

Anytime I see numbers in field names I know it needs a related child table.

But as @plog is saying, nothing you have shown us or told us so far shows you need anything. This is very rare. Most people come here with one table and we suggest them to "normalize" it and create related tables.

 

[June7]

So far, I see potential for 4 tables.

tblFilaments
ID, FilName, ColorID_FK, BrandID_FK, TypeID_FK, Qty, Note
(ColorID_FK, BrandID_FK, TypeID_FK should be maybe be a compound unique index)

tblColors
ColorID, ColorName (values like White1, White2, etc), TD, HEX

tblBrands
BrandID, BrandName, etc

tblTypes
TypeID, TypeName

Can a HEX have different TD values associated with it?
You introduced another acronym - RD - what is that?

 

[Davebert]

@Davebert,
You do not divide into multiple tables just to have multiple tables. You can have 100 columns and if every column uniquely describes that record than it is what is called normal. You create related tables for reasons.

Things where you need multiple tables.
1. Repeated information. For example if you had a lot of information about your "Brand" such as
Address, City, State, POC, Phone Number,....
you do want to repeat this every time in Your data table.
You enter that once in the Brand table.
TblBrand
--BrandID
--Address
--City
--State
--Phone
--POC

In your data table you only hold a Foreign key (brand id). So instead of your current Brand you would have something like BrandID_FK. Which holds the key to BrandID in tblBrand

2. Related Records.
As I said if you wanted to track many notes for your data then you could have a "Child table"
This is often obvious where the table will have something like
Note1 Note1Date Note 2 Note2Date Note3 Note3Date

Anytime I see numbers in field names I know it needs a related child table.

But as @plog is saying, nothing you have shown us or told us so far shows you need anything. This is very rare. Most people come here with one table and we suggest them to "normalize" it and create related tables.

Well that is a surprise. To think that I have stressed over trying to complicate what I had already done correct. Thanks for explaining that in more detail. I guess like I mentioned before I just need to learn how to pull the data I want using forms or queries and call it done. Now I feel like a dummy for trying to fix something that isn't even broke.

 

[Davebert]

No it doesn't make sense. 1 table makes sense. With what you have given us, I see only 1 table, exactly as it is. Again, you only need one table for this data, exactly as you have it now.

I see what you mean and I guess I should have listened but at least I know I already did the flat file db correct. Thanks.

 

[MajP]

Now I feel like a dummy for trying to fix something that isn't even broke.

I will be surprised if in the future you are not needing related tables. Once you get this working you will likely want more functionality or track more information.

Maybe you have multiple images for each item (top view, left side view, right side view)
Now you need a table that is a "Child Table" because one item can have multiple images.
Or maybe you want to track multiple notes. A child notes table.
Or maybe you do need information about the brand. Related brand table.

 

[Davebert]

So far, I see potential for 4 tables.

tblFilaments
ID, ColorID_FK, BrandID_FK, TypeID_FK, Qty, Note

tblColors
ColorID, ColorName (values like White1, White2, etc), TD, HEX

tblBrands
BrandID, BrandName, etc

tblTypes
TypeID, TypeName

Can a HEX have different TD values associated with it?
You introduced another acronym - RD - what is that?

June7,
Now that I understand that what I have already created is perfectly fine, I still want to try and learn some stuff about relational database design and here we have a good place to start. That RD was a typo (edited it). HEX would more than likely have the same or similar value but it's possible it may not based on various manufacturers but for now lets not worry about that. Explain the 3 foreign keys so I know how to use them please.
Thanks