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?
db design question
Moderator: General Moderators
- RobertGonzalez
- Site Administrator
- Posts: 14293
- Joined: Tue Sep 09, 2003 6:04 pm
- Location: Fremont, CA, USA
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);
- kbrown3074
- Forum Contributor
- Posts: 119
- Joined: Thu Jul 20, 2006 1:36 pm