Database Design – How to Store List of Unknown Size

database-design

I have trouble with database design and don't know how to overcome this. The problem is with list of cities between two chosen city. User chooses start and finish city, and OPTIONALLY, user can write which cities between he will pass, so it can be 0,1,…N. How can I store this in my database ? I already have table with list of States and another table with list of cities.

I thought a bit, and if there is no better option, I will have row in which I will store IDs of cities in format "ID1,ID2,ID3,…" and in software I will separate IDs, but I think this is not normal.

Best Answer

You should create another table with three column

Start City [PK]
End City [PK]
Midway City [PK]

you have triple primary key now if you have city_start and city_finish and for example 4 cities between city_start and city_finish then you should add 4 rows to you table like this

(city_start,city_finish,midway_city_one)
(city_start,city_finish,midway_city_two)
(city_start,city_finish,midway_city_three)
(city_start,city_finish,midway_city_four)

now you know that from city_start to city_finish you will visit midway_city_one

Related Solutions

Data base structure of a subscriber list

No RDBMS system I know of supports mixing a data-type within a single column. So I think you've got two solutions:

Separate tables - each user's list gets their own Database table. This would allow you the flexibility to customize the data type at the column level.
Flexible data type in the "name" column. You could use a varchar or even a binary blob in the name column and allow the user to store whatever they want in that column. Then, at the application level, you could interpret that data in whatever format you like by, for instance, casting it to an integer.

It sounds to me, since you're hoping to store different types of data with different structures to it, that you should probably just be using separate tables.

Postgresql – How to design a database for continents, countries, regions, cities and POIs

i suggest 2 options

Option 1: if you want a RBDMs, i think 2 tables will be sufficient

Table 1 (Lookup)
-LocationID
-Continent
-country
-region
-City
-Landmark (granularity)

in this Table since its a lookup so i suggest keeping it Denormalized, it will save you alot in your coding specially when joining tables and searching and reporting. and maintenance is easier, then 5 separate tables

the other table is the table to collect the data

Table 2 (Master)
-Tourist
-LocationID
-ExtraFields
-...

so in this table you collect info about tourists and all you have to do is store the locationid, which is your granularity, this way the Locationid, has the Landmark,city,region,country,continent Since the Landmark is definetly in a city and the city is in a country and the country is in a region.. (no need for 5 tables to keep them separate, there will be a big chance of invalid inputs or data if not maintained properly)

Option 2:

i suggest graph database, check this post at SO , about using Neo4J for GIS and an implementation of GIS by Neo4J

Best Answer

Related Solutions

Data base structure of a subscriber list

Postgresql – How to design a database for continents, countries, regions, cities and POIs

Related Question