Mysql – Separating tables vs having one table

database-designMySQLrelational-theory

At the moment I have a table setup that looks somewhat like this:

create table tbl_locationcollections
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
   name varchar(100) not null,
   cleanname varchar(100) not null,
   typeid int(11) not null (foreign key)
)

create tbl_locationcollectiontypes
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   type varchar(100)
)

a type would be something like: country, state, city etc etc.

I would then join to the various type tables like this:

create tbl_states2locationcollection
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   stateid int(11) not null, --(foreing key)
   locationcollectionid int(11) not null --(foreign key)
)

with other tables like tbl_cities2locationcollection etc etc.

Is this style of seperation better for speed and readability than having a table such as:

create tbl_locations2collection
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary key)
   locationid int(11) not null, --(foreign key to the type of: state, country, city)
   typeid int(11) not null --(foreign key)
)

where all the different types are mixed in together.

the only downside i can see for not having a mixed table, is having to create a specific table for each type that is created in the future.

Best Answer

First of all, mysql (and other sql databases) are RDMS meaning that they are based in the relational model. This means that the design should be about entities and their relations. In your case:

Entities: locations, types.

Relation: one location can be of one type (if I have understand you correctly). This is a one-to-one relation.

The best way to store this relation is as you propose at the begining:

create table tbl_locationcollections
(
   id int(11) PRIMARY KEY IDENTITY, --(Primary Key),
   name varchar(100) not null,
   cleanname varchar(100) not null,
   typeid int(11) not null (foreign key)
)

create tbl_locationcollectiontypes
(
   id int(11) PRIMARY KEY IDENTITY,  --(Primary Key)
   type varchar(100)
)

but then you propose something strange, some tables to relate each type with their locations. There is no need for this table. The relation is already set by the foreign key constraint. If you want to get all the locations of the same type, for example country, you will do it in a query:

SELECT *
FROM tbl_locationcollections
INNER JOIN tbl_locationcollectiontypes 
ON typeid.tbl_locationcollections = id.tbl_locationcollectiontypes
WHERE type.tbl_locationcollectiontypes = 'country'

Benefits of this aproach:

-Readability

-Scalable: you can add more types with out changing the database design

-Logic: you work with relations between entities, wich is the same abstraction our brain makes.

Hope it helps ;) If you want to know how to store one-to-many or many-to-many relations that is another topic, feel free to ask!

Related Question