db design question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

db design question

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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);
User avatar
kbrown3074
Forum Contributor
Posts: 119
Joined: Thu Jul 20, 2006 1:36 pm

Post 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.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

thanks guys... that's what I'll do
Post Reply