Best structure for lots of yes/no's?

[Griphus]

Hello,

Currently working on a database for a "somewhat standard" product with lots of options customers can purchase. Options are almost all "yes/no" type, with a smattering of "if yes, how many" type scenarios. Database uses the product serial number as the primary key.

So, is the best way to handle this to create tables with the serial number as primary key, then a yes/no field for each option? I end up with lots of fields (either in one table or spread across several tables).

Thanks so much!

-Timothy

 

[dhookom]

If you use lots of yes/no fields, you are actually storing data in column names. If you want to add more options, you would need to add more field, controls, etc.

I would probably normalize so that each possible yes/no creates a record in a related, child table.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

As suggested instead of a bunch of fields you would add records to the "Options" table for each selected option. So you would have an option table with an option ID. Then you will need a junction table to form a many to many relationship.

jncTblProductOption
productID_FK (foreign key to the product table)
OptionID_FK (foregn key to option table)

so your data may be like

prod1 opt123
prod1 opt456
prod1 opt789
prod2 opt456
prod2 opt321

The challenge is building an interface to allow you to select a product and an option from the available (not selected) option. We can help once you get your tables correct. This is a common activity, but somewhat advanced.

 

[Griphus]

So, I understand what you are both saying... but I run into an issue because going forward I won't understand how to work with the data.

That is, I won't know how to build a form where a user can put in the data, and I won't know how to use the data the user put in once it's in the database.

Let me tell you more about the business use of the database. I think it may help us further the discussion... this database is going to house historical data regarding time spent building a product by sub-component. So, we have time sheets from a labor force spanning several years. Each time sheet is charged to a serial number and a component. The goal is to put in technical information & options regarding each serial number so that we can begin to try and correlate options to time spent on a component. For example, if the customer purchases "Option 23", "Component 4092" takes 5 hours more, and "Component 3813" takes 2 hours less.

We can then turn around and use that data to both give target times to our labor and also help determine pricing for our customers.

So, this question is a small part of a (relatively) big initiative. I am the "relative technical expert" here, have done all the database work so far, and will probably be responsible for close to 100% of the database work. However, I've got a lot of questions and my ratio of hours of research to breakthroughs / progress is very poor so far.

Anyway, thanks so much for any/all help!

-Timothy

 

[Griphus]

I should clarify that I have used junction tables (once) in the past... but didn't have to build any forms, etc for that database because it was just a work in progress for my personal use.

My understanding is that I would have one very large lookup table of all options - maybe 3 fields (ID, General Category, Specific Option)?

Then a second table which would be my junction table and would essentially just be serial number & option id.

My "primary table" in all of this would be my Commercial Information table, which has information like Serial Number, quantity of units, customerID, etc.

 

[MajP]

You need to get all of your tables correct first. If your design is correct everything falls in place. I do not even worry about the user interface, forms, and reports because I know if the tables are properly constructed (normal) then I can build the forms, reports, and queries to do whatever I want. I will be able to add to the database to provide more functionality. If you start with a bad design, then it becomes one ugly work around after another.

From what you described if you would have made the "options" as fields instead of records, you would have been finished. There is no way you would have been able to query it and do the calculations you describe. If you put in the effort, the experts on this site can and will talk you through anything.

Step one you have to get the table design correct. This is your skeleton. You can read a few threads in this forum to see how people describe their tables and databases. So do the following.
1) Describe the entities that you will have in simple words. In other words the things that you need to hold information about.
example
Products: Holds information that uniquely describes a product
Options: Holds information that identifies an option. Many options can be assigned to a product. Many products can have the same options
Customer: Holds information uniquely describing a customer. A product can have a single customers.
....
2) describe how these things relate to each other. Some of that is already shown above
3) Describe the proposed tables. Include primary and foreign keys at a minimum. You do not have to have exact field names. It is more important that the pseudo field name provides the intent
example:

tblProducts
productID (primary key)
productName (name of the product)
.... other fields uniquely describing a product
customerID_FK ( a key relating a customer to a product)


4)Describe your buisiness process and how the db will be used.

Once you get the feed back on your table

 

[Griphus]

Thanks for the reply - I try to keep my posts relatively short & to the point out of respect for your time. I don't know where else I would get this information if not here, so I try to do whatever I can to make an answer as likely as possible. Based on your suggestion, I typed this up today - hopefully it will help us further the discussion.

1) Project

a. Technical Information

i. Project Number (fk)
ii. Model (fk)
iii. Power
iv. Voltage
v. Diameter
vi. Length
vii. Poles
viii. Power Factor
ix. Enclosure Type (fk)
x. Application / Use (fk)
xi. Noise Limit
xii. Excitation​

b. Commercial Information

i. Project Number (pk)
ii. Quantity
iii. Date of Order (future use)
iv. Contractual Delivery Date (future use)
v. Actual Delivery Date (future use)
vi. Customer (fk) (future use)
vii. End User (fk) (future use)​

c. Electrical Design Data

i. Project Number (fk)
ii. Turns
iii. Layers
iv. Drawing Number
v. Slots
vi. Pounds of Copper​

2) Options (e.g. shipping base, special paint, special crating) (pk) (some of these are yes/no, and some are quantities… what does that look like in a database structure?)
3) Models (pk)
4) Enclosure Types (pk)
5) Applications (pk)
6) Customers

a. CustomerID (pk)
b. Name
c. Contact​

7) End Users

a. UserID (pk)
b. Name
c. Contact​

8) Components

a. Component ID (pk)
b. Component Description​

9) Time Sheets

a. Project Number (fk)
b. Component ID (fk)
c. Time​

10) Routings (operations to complete a component – this is historical data 191,096 rows)

a. Project Number (fk)
b. Componenet ID (fk)
c. Operation Number
d. Operation Description​

End goal is to use project information, data, and options to estimate time for each operation. Operations can then be summed to a component-level time estimate, which can then be summed to a project-level time estimate. Historical routings are not standardized, but a standard routing for each component is being developed in parallel with this database development.

 

[Griphus]

I was worried that would happen. ;-)

 

[MajP]

I assume the project Table has a PK and that is the Project Number. I think the table would look like this

tblProjects (Uniquely defines a project)
Project Number (PK)
Model (fk to model table)
Enclosure Type (fk)
Application_Use (fk)
Customer (fk)
End User (fk)
other fields for data unique to that project

To verify a project has one and only one Model, enclosure type, application, customer, end user.


tblOptions (These are the available options not the selected options)
optionID (Pk) I would not use the name as the PK. Probably an autonumber
optionDescription (special paint, special paint)
optionCategory (may be helpful)

I would not combine yes no and quantity. I it is yes then make the quantity one.
On the user interface you can make the default 1.

So the junction table is
tblProject_Options
projectID_fk (fk to project table)
optionID_fk (fk to option table)
optionQuantity

assume project a,b,c options 1,2,3
you could then have
a 1 10
a 2 1
b 1 3
b 2 1
b 3 1

So then A has 10 of option 1, and selected option 2
B has 3 of option 1, selected option 2 and 3


Now making the interface would likely be a form with a subform. You would see the project name, and have the ability to Add options by name (of those not yet selected), and change the quantity from 1 to something else. Think of it as adding available options not as checking them yes or no.


I do not understand your time tracking. I would think time is tracked against an operation for a specific component on a specific project. The rolled up estimates should not be fields in the table. So the time sheets table does not make sense nor the routings. You will have to explain where time is tracked and how it rolls up.

 

[Griphus]

Hey there,

Thanks very much for the response.

If you were asking me to verify that any one project has only one model, enclosure type, application, customer, end user. Yes, that is correct. (One project can only have one voltage, diameter, length, number of poles, noise limit, etc. I've been calling these the "critical parameters" for lack of a better way of defining them.)

For options that are a "Yes or no" type decision in the real world, we would just take "1" in the quantity to mean "yes"? I am trying to think of a way to control it so the operator couldn't accidentally make it something other than 1 (or make it so that anything other than zero means "yes").

On to the form / UI... I can visualize what the top part of the form would look like, but I can't see in my head how the subform would look... something like two dropdown boxes with a text entry box? (1 drop down for category, another for option, then the text entry for the quantity?) I wouldn't know how to filter it to make it only show those not selected, or how to enable someone to delete them if they accidentally added the wrong one.

Regarding the time tracking... I understand the way it was done, but I don't understand why they did it this way (this all happened long before I started working here). It makes no sense to me, but we are now in this position and need to fix it. I'll describe it a different way...

What we have (past data)

Component (time data)
-Operations

for example,

Component ID: 2036, Component Description: Shaft Machining, SUM of Time: 43 hours
Operation: 010, Op Desc: Purchase Forging
Operation: 020, Op Desc: Machine Ends & Rough mill
Operation: 030, Op Desc: Drill Cham & Tap for lifting
...

Going forward, so no data yet, (we have now implemented SAP), we need to estimate these at the Operation level. So that 43 hours of actual data would be averaged with data from other similar projects, and then we'd break it down into the hours estimated for each Operation using all sorts of math based on which Options (and how many, if applicable) were purchased. Again, I don't know WHY it was done this way... but I know how it was done.

Hope that helps! Let me know if you have any other questions. On my end, I am going to restructure the database per your suggestion(s).

Thanks again,
-Timothy

 

[MajP]

For options that are a "Yes or no" type decision in the real world, we would just take "1" in the quantity to

mean "yes"? I am trying to think of a way to control it so the operator couldn't accidentally make it something

other than 1 (or make it so that anything other than zero means "yes").

You could control this at the user interface. In your options table you need to add a boolean field
optionHasQuantity
This field would have a Yes field means it requires a quantity and a no means it is a "Yes No" field


On the user form you can use some code to not allow them to enter the quantity of the "Yes No" options and

require a quantity for the "Quantity" options. But, the options will still get stored in the same table.

On to the form / UI... I can visualize what the top part of the form would look like, but I can't see in my

head how the subform would look... something like two dropdown boxes with a text entry box? (1 drop down for

category, another for option, then the text entry for the quantity?) I wouldn't know how to filter it to make

it only show those not selected, or how to enable someone to delete them if they accidentally added the wrong

one.

I was suggesting a field of Category only to group your options. Lets assume you have a lot of paint options you may want to sort or group on category "Paint".

To show those not selected, your query would use a "not in" predicate to not include options already part of

the project. W we can explain that. But in general it looks like
Select optionID, other option fields where optionID not in (Select option id from tblProject_Options where

ProjectID = forms![yourFormName].ProjectID]
If this is the rowsource of the combobox you can only select options not yet selected.
You can get fancier and use code to do things like checked listboxes or "to from" listboxes. As long as the data structure is correct you can come up with many types of interfaces.

Still trying to wrap my head around your times.

 

[Griphus]

Got it. Thanks. I've got the options table built and I'm filling it in right now. We have something like 80 "options."

Is there something more I can tell you about our time entries that would help you understand? I could show you some very limited part of the dataset or something if it would help.

 

[MajP]

If I understand. A component for a given project has Operations associated with it. Operations are basically tasks that require time to complete. In the past the time to complete an operation was not tracked, the roll up was only tracked at the component level, but you want to estimate it at the operation level. In the future do you want to start tracking time at the operation level?

You may need another many to many table. Do projects have multiple components and can components belong to multiple projects. Or are components unique to a project? If it is the first case then you need a junction table to assign components to projects

 

[Griphus]

Yes, your understanding is very close. A Component for a project has Operations associated with it. A Routing (also called a Production Order) has a list of Operations that will be performed either on a Component or to make a Component.

Correct, Operations take time to complete (as opposed to a material, which just costs money and doesn't have a time measurement associated with it). Correct, in the past the time to complete an operation was not tracked, the roll up was only tracked at the component level. Correct, we now want to use that historical data to estimate at an Operation level.

Yes, in the future we do want to track time at the operation level so that we can see our variance between what we estimated and what is actually recorded. This will guide us to where our estimate was wrong or where there are issues with the labor force.

Your last question is a very good one with a sort of interesting answer. An example of a component is "Rotor Assembly." For context, this is the work needed to put together the spinning portion of a large electric motor. So, every project has a "Rotor Assembly" component, but because of all the options (which we discussed earlier), "Rotor Assembly" for one project might be quite different from "Rotor Assembly" for another project. However, the time would still be charged to the same component number. This is all for the historical data. I'll actually need to ask some people about the current way this information is organized.