[SOLVED] Serialize or not to serialize...?

[Yesideez]

I'm making a database for a project I'm working on and I'm unsure which is the best method to use.

 

Basically I've got 10 locations and a list of about 15 or 20 items for sale in each location.

 

Is it best for me to add each item as a field in the locations table (so I can list each price for each item in each location) or to have a varchar for each location and serialize the contents into that?

 

Hope I've explained myself well enough...

[chigley]

Make a locations table - make sure your locations have an ID.

 

Make an items table with a field for item name and then locationid.

 

When querying them [pre]SELECT * FROM items WHERE location = 1[/pre]

 

That's what I'd do

[Yesideez]

This is my states table as it stands...

CREATE TABLE `states` (
  `stateid` tinyint(2) unsigned NOT NULL auto_increment,
  `name` varchar(9) NOT NULL,
  `abbrev` varchar(2) NOT NULL,
  PRIMARY KEY  (`stateid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

-- 
-- Dumping data for table `states`
-- 

INSERT INTO `states` VALUES (1, 'Arizona', 'AZ');
INSERT INTO `states` VALUES (2, 'Arkansas', 'AR');
INSERT INTO `states` VALUES (3, 'Colorado', 'CO');
INSERT INTO `states` VALUES (4, 'Idaho', 'ID');
INSERT INTO `states` VALUES (5, 'Minnesota', 'MN');
INSERT INTO `states` VALUES (6, 'Missouri', 'MO');
INSERT INTO `states` VALUES (7, 'Nevada', 'NV');
INSERT INTO `states` VALUES (8, 'Utah', 'UT');
INSERT INTO `states` VALUES (9, 'Wisconsin', 'WI');
INSERT INTO `states` VALUES (10, 'Wyoming', 'WY');

 

Just wondering if I should create about 15 or 20 extra fields with the names of each item for sale so I can list all the prices for all states or whether one varchar field would be better with the array serialized...

[taith]

if your wanting dynamic info... a text column with a serialized array, would prolly be your best bet :-)

[Yesideez]

That's what I was thinking mainly because of the number of items for sale and the number of states. Pulling the data from separate fields would require a little more coding..

[taith]

ya... really depends on how complex you want it... if you just want to show the items... then ya... serialize'd be the best... if you want to be able to sort, and limiting, and the likes... you'd want it set in its own table :-)

[Yesideez]

No sorting it needed. All I need to do with the data is pull it out and view it. A cron job may later be set up to make the values fluctate, not decided on that yet.