how to streamline a potentially huge set of fields

[turpentyne]

I'm very new at setting up databases and php.

 

I want set up a table that breaks down where certain items are located.

 

The highest level is continents (7)

Next is subcontinents (about 30)

from there, countries (200)

Then..gasp...departments/states/provinces (hundreds? Thousands?)

 

Where I really run into a problem, is that these items can be in several of these places across the globe, so I'd need fields that were simply true or false.

 

So I started realizing I'd have to create some 2000 fields or so of place locations. egads! At the very least I'd have to make a huge amount of tables with lots of names in them.

 

Is this what I'd have to do to follow the proper normalization? Am I overlooking a much more simple solution? Any ideas?

 

Thanks!

[Mchl]

An example perhaps of how you're imagining it now?

[turpentyne]

Here's a quick little sample:

 

a table called geography, with 3 items in it. Item 100 is in parts of the US and Canada, Item 102, different parts of US and Canada, Item 103 in parts of the US and Mexico

At this current structure, i would end up with a thousand fields set to true or false, just to cover all locations. Maybe it's that simple, or maybe I'm just thinking too simply about it.

 

CREATE TABLE IF NOT EXISTS `geography` (

  `id` smallint(6) NOT NULL auto_increment,

  `item_id` smallint( NOT NULL,

  `canada` varchar(100) NOT NULL,

  `USA` varchar(100) NOT NULL,

  `Mexico` varchar(100) NOT NULL,

  `Alaska` varchar(100) NOT NULL,

  `British Columbia` varchar(100) NOT NULL,

  `Washington` varchar(100) NOT NULL,

  `Oregon` varchar(100) NOT NULL,

  `California` varchar(100) NOT NULL,

  `Arizona` varchar(100) NOT NULL,

  `Sonora` varchar(100) NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

 

 

 

INSERT INTO `geography` (`id`, `item_id`, `canada`, `USA`, `Mexico`, `Alaska`, `British Columbia`, `Washington`, `Oregon`, `California`, `Arizona`, `Sonora`) VALUES

(1, 101, 'true', 'true', 'false', 'true', 'true', 'false', 'false', 'false', '', ''),

(2, 102, 'true', 'true', 'false', 'false', 'true', 'true', 'true', 'false', '', ''),

(3, 103, 'false', 'true', 'true', 'false', 'false', 'false', 'false', 'true', 'true', 'true');

 

[turpentyne]

sorry about the smiley face. Didn't expect that. It's supposed to be 8 and )

 

 

[DavidAM]

You are going to have to DESIGN the database. I thought you were on the right track when you said:

The highest level is continents (7)

Next is subcontinents (about 30)

from there, countries (200)

 

But then you went off to put everything in one table. Basically, I would start DESIGNING this database as:

CREATE TABLE Items(
  ItemID INTEGER AUTO_INCREMENT,
  Description VARCHAR,
  PRIMARY KEY (ItemID) );

CREATE TABLE Continents (
  ContinentID INTEGER AUTO_INCREMENT,
  ContinentName VARCHAR,
  PRIMARY KEY (ContinentID) );

CREATE TABLE SubContinents (
  SubContinentID INTEGER AUTO_INCREMENT,
  ContinentID INTEGER, -- Shows which continent this subcontinent is on
  SubContinentName VARCHAR,
  PRIMARY KEY (SubContinentID)
  FOREIGN KEY FK_SubsContinents_Continents (ContinentID) REFERS TO Continents
    ON UPDATE CASCADE ON DELETE CASCADE) );

-- Then Countries which refers to the Subcontinents
-- Then Provinces (States) which refers to the Countries
-- Then the next level
-- And so on

CREATE TABLE ItemLocation (
  ItemID INTEGER,
  LocationID INTEGER, -- Refers to your lowest level Location table (States or Departments or whatever)
  ... -- Any other fields that are needed at this level
  PRIMARY KEY (ItemID, LocationID)
  FOREIGN KEY ... REFERS TO Items
  FOREIGN KEY ... REFERS TO Location );

 

Of course that is my first pass. I would also look at the application requirements and the expected data to determine just how this breakout needs to be designed. For instance, are all countries on a sub-continent? or would that have to be referenced differently? There are other similar questions that would need to be answered.

 

But NO! you do NOT want to put all of those "Locations" as individual columns in ONE table.

[Mchl]

Well yes... you're doing it against normalisation rules.

 

You could for example create tables like this

 

table places

place_id, name, ...

---------------

1, Canada

2, USA

3, Mexico

....

 

table items

item_id, name, ...

------------

1, item1

2, item2

3, item3

....

 

table items_in_places

item_id, place_id

------------------------

1, 1

1, 2

2, 2

2, 3

3, 1

3, 3

 

So item1 is in Canada and USA, item2 is in USA and Mexico and item3 is in Canada and Mexico

 

This way you can easily add new places and also easily check what items are in what places.

[turpentyne]

I suspected originally that I had to break it down into tables for each item. Like building the database items to populate a tiered dropdown menu, right?

 

But I'm still not understanding something. The way it is in my brain, even if I break the geographic locations into individual tables, I'd still have an item_table full of integers representing  several subcontinents, countries and provinces.

 

I am at work and distracted by other things, so maybe that's why I'm not grasping this. hah!