Can anyone suggest an idea for how to organize this data: I have 700 different variations of the same type of electrical component. Each one of these has about 100 specifications, eg, power consumption, heat dissipated, voltage, etc etc. The categories of specifications are going to be exactly the same for every component. I'm hopefully going to import it from excel. After the data is in tables, it needs to be possible to search for averages, max, mins, all possible trends in the data. I want to be able to do a query and see exactly which of the 700 components has the best operating temp for example. I apologize if this is incoherant, I got a new puppy last night and got about 35 minutes of sleep in total last night.
700 different components, all with the same specs...table structure?
- Thread starter JTQ911
- Start date
georgedwilkinson
AWF VIP
- Local time
- Today, 10:17
- Joined
- Mar 4, 2008
- Messages
- 3,819
There are a couple of ways to look at this, based on the purpose of your database.
If this data is just going to be reported on, you need to keep it flat (like in Excel).
If you are going to be using this data for day to day operations (OLTP), you need to normalize the database. This might include having a table to hold the electrical component, another table to hold the specs, and a third to hold a type to discriminate the data in the specs table. Each row in the specs table would only have one specification (i.e. operating temperature).
Either way, you can run the queries you're talking about.
As my friend missinglinq says, there's more than one way to skin a cat.
If this data is just going to be reported on, you need to keep it flat (like in Excel).
If you are going to be using this data for day to day operations (OLTP), you need to normalize the database. This might include having a table to hold the electrical component, another table to hold the specs, and a third to hold a type to discriminate the data in the specs table. Each row in the specs table would only have one specification (i.e. operating temperature).
Either way, you can run the queries you're talking about.
As my friend missinglinq says, there's more than one way to skin a cat.
- Local time
- Today, 10:17
- Joined
- Feb 28, 2001
- Messages
- 30,566
There is the brute force approach, in which you have number or numbers representing the range of data. Say, operating temp = -20 to + 60 (degrees C), allowed voltages = 105 to 135 V - this would require four integer fields holding -20, 60, 105, 135... in the same record.
OR you might have some of these that are analogous to tolerances in a resistor. You get 10%, 5%, 2%, 1%, or finer tolerances. In other words, the range is plus or minus some range around the nominal value and there aren't that many ranges. So instead of saying 105 to 135 you might have a code that says "plus or minus 15 volts" and another that says "plus or minus 10 volts." In which case you store that as 1 digit to specify which of several ranges you meant.
If you do the latter, you have smaller records = faster/easier to search. If you do the former, you have larger records but the fields are unequivocal.
OR you might have some of these that are analogous to tolerances in a resistor. You get 10%, 5%, 2%, 1%, or finer tolerances. In other words, the range is plus or minus some range around the nominal value and there aren't that many ranges. So instead of saying 105 to 135 you might have a code that says "plus or minus 15 volts" and another that says "plus or minus 10 volts." In which case you store that as 1 digit to specify which of several ranges you meant.
If you do the latter, you have smaller records = faster/easier to search. If you do the former, you have larger records but the fields are unequivocal.
Similar threads
