I'm developing a database spec intended for use creating databases of detailed technical specifications of products. For example, say I'm making a database of technical specs for cars.
Currently, there are a couple of issues I'm dealing with. For one thing, the main database table containing the car entries has about 80 columns. This is bad database design from what I hear, but when you're creating complex data objects what other options do you have? One option could be to create sub-tables that reference part of a car's technical specs, but that would get complicated quickly.
For instance, instead of having a car's engine specs be part of the main car table, I could just create a table of engine specs containing engine statistics like volume, number of cylinders, material, etc. and link entries in that table with the main car table. I could do the same thing for transmission systems, body styles etc. The only problem is that the code is relatively simple when all the car data is in one table and breaking the table up will make things a lot more complicated. I've created a Web interface for adding new entries to the car table, and if I ended up using multiple tables to represent a single car the code could get very confusing. What's the right way to deal with a situation like this? Is it okay to have a ton of columns in this kind of application?
The second issue I'm having is that when I use keys to pull names for car attributes from linked tables, I end up making tons of queries. Right now I use a system where the columns in the main car table contain integer keys which reference tables of names. For instance, a car's body style column in the main car table could have a value like "2", which would correspond to "Coupe" in the table of car body style names. The thing is that since I have about 70 integer key columns in the main table, I have about 70 corresponding name tables, and whenever I pull the data for a given car I end up doing 70 queries to fetch the data from all these name tables.
The normal way to query multiple tables is to join them, but joins seem to be intended for situations where you're looking for correlations between many rows in a few database tables, not situations where you're linking many values from one row of a table to row IDs in many different smaller tables. It would be nice if I could copy data from many tables into a multidimensional array with a single query, but I haven't found a way to do it.
Is there a way to grab key references from a large number of name tables in a single query? Thanks for any answers you can provide.