I’m building a “car part” inventory system… The system contains thousands of parts. However, each part has completely different criteria… I.e. a Tire might have max pressure, expiration, size, tread, etc… whereas a car battery might have voltage, expiration, etc…
Many fields are different, but many are the same. So for example, if I wanted to search my inventory database for all parts that are expiring soon, I’d want
both the battery and the tire to return in the results.
My question is, what is the best way to organize this in a database? Here are the three possibilities that I’ve come up with so far:
Have all possible fields in one table regardless if only some are used
Pros: Only 1 table is needed, and only 1 row is required for each part entry.
Cons: Could be up to like 100+ fields in the parts table. Only some are used for each part, based on the type.
Have a table for each type of part.
Pros: Only needs the specific columns that are required for this type of part.
Cons: Will have a lot of tables, one table for each type of part.
Have a table every field be custom, and store the needed fields in a global database for each entry.
Pros: Only need 1 record in the master table for each part.
Cons: The many-to-many table that holds the part, field id, and value, would be large. Each part entry could have up to 50 fields that are entered.
So my question is, which idea is the best? Maybe something completely different? Keep in mind that this will house like 100,000 parts, so speed is very important. Any suggestions on how to structure this database so its the most efficient for storage, and most importantly, the most efficient for searching across all parts in the database for the matches.