Structure question - which method is better for storing changing fields

Dev Shed Forums Sponsor:

Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!

January 30th, 2003, 06:25 AM

RyanA

Junior Member

Join Date: Jul 2002

Posts: 23

Time spent in forums: < 1 sec
Reputation 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(6) NOT NULL default '0',

                  name varchar(255) NOT NULL default '');

CREATE TABLE user (id int(6) NOT NULL default '0',

                   name varchar(255) NOT NULL default '');

CREATE TABLE owned (id int(6) NOT NULL default '0',

                    car_id int(6) NOT NULL default '0',

                    user_id int(6) NOT NULL default '0');

Would it be better to create it as..

PHP Code:


		
			
 CREATE TABLE user (id int(6) NOT NULL default '0',

                   name varchar(255) NOT 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.

January 30th, 2003, 07:35 AM

victorpendleton

Contributing User

Join Date: Jan 2003

Location: No es importante

Posts: 2,065

Time spent in forums: 6 h 31 m 56 sec
Reputation Power: 8

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

January 30th, 2003, 10:29 AM

bcyde

Me likey breadsticks...

Join Date: Jan 2003

Location: Los Angeles

Posts: 1,189

Time spent in forums: 3 Days 1 h 12 m 57 sec
Reputation Power: 12

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...

Viewing: Dev Shed Forums > Databases > Database Management > Structure question - which method is better for storing changing fields

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump