#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    23
    Rep Power
    0

    Structure question - which method is better for storing changing fields


    Lets say we have a set of tables setup to keep track of what cars a person has but the person can only enter car types that are predefined from a check box:

    PHP Code:
    CREATE TABLE car (id int(6NOT NULL default '0',
                      
    name varchar(255NOT NULL default '');
    CREATE TABLE user (id int(6NOT NULL default '0',
                       
    name varchar(255NOT NULL default '');
    CREATE TABLE owned (id int(6NOT NULL default '0',
                        
    car_id int(6NOT NULL default '0',
                        
    user_id int(6NOT NULL default '0'); 
    Would it be better to create it as..

    PHP Code:
    CREATE TABLE user (id int(6NOT NULL default '0',
                       
    name varchar(255NOT NULL default '');
    CREATE TABLE car (user_id NOT NULL default '0',
                      
    $car_name varchar(255) default NULL,
                      
    $car_name varchar(255) default NULL,
                      
    $car_name varchar(255) default NULL); 
    .. and add new default NULL fields to the table as more cars are added? I began with the first method, but I'm leaning more twards the second now for simplicity's sake.

    What are the reasons that I might want to avoid one method and use the other?

    Is there a better method?
    Last edited by RyanA; January 30th, 2003 at 06:29 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    Separating each unrelated entity is the best method and will move you toward normalization of your data.
    ...
    The first method will allow you to change a car independently without having to modify a user. It will also allow you to define cars even before they are owned by a person.
    ...
    Your second method closely resembles a flat file. This type of file is 'good' for searching but can very quickly become inefficient for updates and inserts.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  4. #3
  5. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    Go with the first way. The second way violates the first rule of data normalization.

    Check out here for more info regarding data normalization.
    PostgreSQL, it's what's for dinner...

IMN logo majestic logo threadwatch logo seochat tools logo