Question about mySQL tables with many columns

[TheGardener]

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.

[Shadowfiend]

It'll make things more complicated, but more maintainable and faster while you're at it. Joins are, in fact, meant for both of the situations you described. Unions may also prove useful.

[TheGardener]

Well, I just figured out that code like this will work fine for matching keys with names from multiple tables:

        $query = "SELECT ";
        
        $query .= "list_orgs__manufacturers.name, ";
        
        $query .= "list_models__popularDesignations.name ";
        
        $query .= "FROM ";
        
        $query .= "list_orgs__manufacturers, ";
        
        $query .= "list_models__popularDesignations ";
        
        $query .= "WHERE ";
        
        $query .= "list_orgs__manufacturers.ID = ".$result_row[2]." ";
        
        $query .= "AND ";
        
        $query .= "list_models__popularDesignations.ID = ".$result_row[7]." ";

I'm still not sure why it's better to have sub-tables for every component of a car t than it is to have one big table with a lot of columns. The whole reason I'm going for the big table is to make it easily maintainable and portable, since I won't have to remember a huge spaghetti-like network of table associations when I go to modify the database functions.

My Web-based database entry tool works by grabbing the titles from the sub-tables and automatically creating select dialogs and text fields using them, and if I decide to create separate tables for car engines and car bodies and whatnot, it's going to be hard to set up and maintain a data entry tool for adding new cars to the database.

Also, doesn't a union involve multiple queries put together? Does performing a union take as much CPU time as performing the same number of queries separately?

[Shadowfiend]

Well, unions are for when you need the two datasets together. Typically with multiple tables you can reuse entries in one table for other tables.