Page 1 of 1

db design question

Posted: Thu Jul 20, 2006 5:37 pm
by Luke
I would like some advice on a database I am designing. It is for a site that will use google maps to map locations for customers. My question relates to the bottom table listed below. This table (points) is really the heart of this database. It will hold the lat-lon locations of ALL different types of points on my map. My question is... would it be wiser to have a seperate table for Historical, Nature, and Wildlife... (which are types of points) so that it is possible to add more types of points? I only ask because I am not entirely sure I will need to add types of points, and I'm not sure if this constitutes adding an entire table to the database. Any advice is welcome. Thanks!

Tables:

Businesses:
• Date Submitted
• Name of submitter
• Business name
• Business owner’s name
• Address
• City
• State
• Zip
• County
• Offerings
• Season
• Hours
• Driving directions?
• Phone
• Fax
• Email
• Website Address
• Point id

Corridor:
• Information about corridor (not sure what will be in here yet)

Events:
• Name
• Phone1
• Phone2
• Point id

Points
• Latitude
• Longetude
• Corridor id
• Id
• Historical?
• Nature and wildlife?
• National forest/park?

Posted: Thu Jul 20, 2006 6:32 pm
by RobertGonzalez
I would make it an id field that relates to another table with the id's described. Seems like an appropriate use of the relational features of an RDBMS.

Posted: Fri Jul 21, 2006 7:20 am
by GM
I'd keep all points in the same table, regardless of the type of point, and have a column that describes what kind of point it is. Make sure that you index that column if you are going to be doing searches by point type (ie: show me all "History" points etc.)

Code: Select all

CREATE TABLE t_points (
lattitude ...
longitude...
corridor_id ...
id ...
point_type CHAR(3) NOT NULL,
PRIMARY KEY (???));

CREATE INDEX idx_points_point_type ON t_points(point_type);

Posted: Fri Jul 21, 2006 12:39 pm
by kbrown3074
Keep the table the way it is and have different types of points like GM said. Much easier to add another field instead of a whole new table for any new points you might come up with. You can also query the different types of points straight from this table instead of accessing multiple tables.

Posted: Fri Jul 21, 2006 12:41 pm
by Luke
thanks guys... that's what I'll do