Simple MySQL database question

Question

Hi, I'm very new to databases and I have a quick question.

How would I design my MySQL database if I have these fields:

ID, lat, long, date - multiple dates, time - multiple times

I know I should put it into two tables, right? And how would those two tables look?

Thanks!

Answer 1

Your first table might be called "location" and it would have an "id" column as its primary key, along with two columns called "latitude" and "longditude" (which could be varchar or a numeric type, depending what your application requires). Your second table might be called "location_event" and it could have an "id" column as its primary key, along with a foreign key column called "location_id" that is a reference to the primary key of the "location" table. This "location_event" table would also have a "date" column and a "time" column (of types date and time respectively).

Answer 2

As far as I can guess the date en time are couple always appearing together. In that case I would suggest two tables, location and time.

CREATE TABLE location (
  id INT  NOT NULL,
  lat FLOAT  NOT NULL,
  long FLOAT  NOT NULL,
  PRIMARY KEY (id)
)


CREATE TABLE time (
  id INT  NOT NULL,
  locationid INT  NOT NULL,
  date DATE  NOT NULL,
  time DATE  NOT NULL
)

Optionally you can add a foreign key constraint

ALTER TABLE time ADD CONSTRAINT location_fk_constraint FOREIGN KEY location_fk_constraint (locationid)
    REFERENCES location (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

Answer 3

OK, let's say, for the sake of argument, that you are talking about longitude and latitude. That these are entries in some kind of list (perhaps a sea log? Arrgh, me maties!) of longitude and latitude. And that each of these long/lat pairs may appear more than once in the list.

Perhaps you want to build a database that figures out how many appearances each long/lat pair has, and when each appearance happened?

So how's this: First we have a table of the long/lat pairs, and we'll give each of those an ID.

ID  long   lat
--  -----  -----
1   11111  22222
2   33333  44444
3   55555  66666

Next, we'll have another table, which will assign each appearance of the long/lat pairs a date/time:

ID  date     time
--  ----     -----
1   1/1/1900 12:30
1   2/2/1900 12:31
1   3/2/1900 12:30
2   1/1/1930 08:21

Let's say you'll call the first table "longlat" and the second one "appearances".

You could find all the appearances of a single long/lat pair by doing something like:

SELECT date,time FROM appearances 
   LEFT JOIN longlat ON appearances.ID=longlat.ID 
   WHERE longlat.long = 11111 AND longlat.lat = 22222

You could count how many times something happened at a longitude of 11111, by doing:

SELECT count(ID) FROM appearances 
   LEFT JOIN longlat ON appearances.ID=longlat.ID 
   WHERE longlat.long = 11111

Hope that helps! I gotta admit, it's really quite annoying to try and guess what people mean... Try making yourself more clear in the future, and you'll see that the help you'll get will be that much more useful, concise and targeted at what you need.

Good luck!

Answer 4

It's hard to tell what you're trying to do from the terse description but third normal form dictates that any column should be dependent on:

• the key.
• the whole key.
• nothing but the key.

To that end, I'd say my initial analysis would generate:

Location
    LocId primary key
    Lat
    Long
Events
    LocId foreign key Location(LocId)
    Date
    Time

This is based on my (possibly flawed) analysis that you want to store a location at which zero or more events can happen.

It's good practice to put the events in a separate table since the alternative is to have arrays of columns which is never a good idea.

Answer 5

As far as I can guess the date en time are couple always appearing together. In that case I would suggest two tables, location and time.

CREATE TABLE location (
  id INT  NOT NULL,
  lat FLOAT  NOT NULL,
  long FLOAT  NOT NULL,
  PRIMARY KEY (id)
)


CREATE TABLE time (
  id INT  NOT NULL,
  locationid INT  NOT NULL,
  date DATE  NOT NULL,
  time DATE  NOT NULL
)

Optionally you can add a foreign key constraint

ALTER TABLE time ADD CONSTRAINT location_fk_constraint FOREIGN KEY location_fk_constraint (locationid)
    REFERENCES location (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;