Best structure for lots of yes/no's?
Best structure for lots of yes/no's?
(OP)
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
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
RE: Best structure for lots of yes/no's?
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
RE: Best structure for lots of yes/no's?
http://r937.com/relational.html
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: Best structure for lots of yes/no's?
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.
RE: Best structure for lots of yes/no's?
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
RE: Best structure for lots of yes/no's?
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.
RE: Best structure for lots of yes/no's?
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
RE: Best structure for lots of yes/no's?
1) Project
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
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)
ii. Turns
iii. Layers
iv. Drawing Number
v. Slots
vi. Pounds of Copper
3) Models (pk)
4) Enclosure Types (pk)
5) Applications (pk)
6) Customers
b. Name
c. Contact
b. Name
c. Contact
b. Component Description
b. Component ID (fk)
c. Time
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.
RE: Best structure for lots of yes/no's?
RE: Best structure for lots of yes/no's?
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.
RE: Best structure for lots of yes/no's?
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
RE: Best structure for lots of yes/no's?
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").
[quote]
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.
[quote]
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.
[quote]
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.
RE: Best structure for lots of yes/no's?
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.
RE: Best structure for lots of yes/no's?
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
RE: Best structure for lots of yes/no's?
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.