CODEWITHSUNDEEP
mysqldatabase-designgeolocation
user1122510
user1122510

Reputation: 11

Tables inside tables in MySQL?

I have set up a MySQL database for location tracking on several devices. Currently, I plan to have each device having their own table in which to store their locations at various times. I was thinking that in a device's table, there would be a timestamp table which would hold latitude, longitude, and other identifying information. This is my first time using MySQL, so I'm sure this isn't the best way to do it...but is this possible? If not, what are some other alternatives?

Upvotes: 1

Views: 1572

Answers (3)

Anatoly
Anatoly

Reputation: 15530

With according to Flickr experience with MySQL, it can be used as schema-less as well, just start keep serialized data in a column if you know what you want.

MySQL is primarily production-ready solution with ready-to-use tools to backup, recover, replication and sharding tool which is ideal to maintenance. If you don't want to use schema, just don't use it

Upvotes: 0

shylent
shylent

Reputation: 10086

From what I can see, your database schema should look more or less like this:

create table devices (
    id int not null auto_increment primary key,
    name ...,
    ...
) engine=INNODB;

create table device_locations (
    id int not null auto_increment primary key,
    device_id not null,
    lat ...,
    lng ...,
    ...,
    index ix_device_id(device_id),
    foreign key (device_id) references devices(id) 
) engine=INNODB;

What you want to do is perfectly suitable for a relational database (such as MySQL, hey it even has built-in datatypes just for storing GIS data), however you should probably read up on normal forms first.

Upvotes: 1

Joshua Partogi
Joshua Partogi

Reputation: 16425

Try NoSQL databases that doesn't restrict schemas. MongoDB comes in mind when you want to structure nested collection.

Upvotes: 0

Related Questions