Designing a table in mysql.

Question

Mysql , php newbie here. Please be nice .

I have a list of colonies - colony1 , colony2,..., colony100.

Say Colony5 is nearby to colony4, colony9 and colony10. Colony4 is nearby to colony5, colony9, colony10 and colony11 . Different colonies have different number of nearby colonies.

How do I store and fetch this data in mysql ?

Currently I am thinking a table that would look like this ->

id | colony name | nearby_colony_id_1 |nearby_colony_id_2 |nearby_colony_id_3 | nearby_colony_id_4

Is there a better way of doing this ? I hope my question was clear.

Answer 1

You need two tables. The first will hold the colony specifications (id, name, ...), the second will hold links between colonies. In the first table you will have one row per colony.

Ex:

Id Name
1  colony1
2  colony2
3  colony3
4  colony4

In the second table, you will have a column for the id of the colony, a column for the id of the nearby colony. In this table, you will have one row per colony neighbour.

Ex (here colony1 has colony 2 and 3 as nearby colonies):

ColonyId NeighbourId
1        2
1        3

You can use foreign key to ensure that the colony referenced in table 2 does exist in table 1.

Answer 2

Designing tables is fun!

If you want to store data where things are "nereby," you can do it with a graph (data structure). If you don't care about the specifics of nearby and only that a colony is nearby, you can just do this with another table. Do not do what you are trying to do (id_2, id_3, etc.) This is not a normalized DB and can lead to anomalies. A lot of people make this mistake the first time.

CREATE TABLE
   Colonies (`id` int unsigned NOT NULL auto_increment, `name` varchar(255));
CREATE TABLE
   Nearby_colonies (`a_id` int unsigned NOT NULL, `b_id` int unsigned NOT NULL);

So you have your colonies, in the colonies table. Then, for every nearby colony to that colony, you have an entry in Nearby_colonies that has a pair of IDs (order should not matter, but the names have to be different). This links the two colonies as Nearby colonies. Now one colony can have as many Nearby_colony entries as it likes instead of being limited to id_2, id_3, id_4, etc.

This also prevents anomalies from occurring because the relationship itself is stored for each colony, not just for one colony about another.

If you want to get even more specific and store the distance between the two colonies, no problem! Just add another field to Nearby_colonies to do that. Obviously the distance from colony a to colony b is the same in either direction ;P

Answer 3

i guess that you already have the answer, but it's important to understand the idea behind the solution so in the future you could do it yourself

You have a class named colonies that is related to itself (e.g "a colonie is nearby multiple colonies") in a many-to-many relationship. That's called NxN recursive relationship. in a simple UML diagram it'd be something like

now that you have the objects model, it'd be easier to create the db tables. A NxN relationship could be interpreted as an intermediary table that contains both ids as the primary key. In this case we'll need a table that i'll call tbl_nearby

witch is basically @tandu's answer, but i prefer using foreign keys because it will preserve the data integrity.

its a good idea to use UML objects model and then translate that model into database tables because that way it'd be easier for you to design really complex models

Good luck