Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 4 of 4
  1. #1
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    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.

  2.  

  3. #2
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    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.

  4. #3
    Member TheGardener's Avatar
    Join Date
    Jun 2006
    Posts
    62
    Member #
    13332
    Well, I just figured out that code like this will work fine for matching keys with names from multiple tables:

    Code:
            $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?

  5. #4
    Senior Member
    Join Date
    Jun 2005
    Location
    Atlanta, GA
    Posts
    4,146
    Member #
    10263
    Liked
    1 times
    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.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 02:06 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com