I feel that spatial databases should be treated no differently to traditional databases. They are essentially doing the same thing, storing large amounts of data for fast retrieval. As an example, in PostgreSQL / PostGIS, the geometry is just another datatype. Just like text, or integer. Same in SQL Server 2008. Same in Oracle. If the "spatial" part is just another field type in the database, then is it really that different from the original database? Does this mean we should throw out all the rules of traditional database design?
Obviously normalization can be taken too far, just as with traditional databases, so it's a trade off to find the best design that suits your needs.
If you are planning on created a highly de-normalized structure with tables of 100 columns, then you have to ask yourself what is likely to change in the future? With a vast increase in rows, is this also going to affect querying performance? Is this going to affect maintainability in the future?
What's wrong with created a normalized structure and using views to expose all the data to the database client, be it GIS, or any other client?
All of these questions apply to both traditional databases and spatial databases. If you go through http://en.wikipedia.org/wiki/Database_normalization you will find that it applies to spatial databases as well.
If the software you are using on top of the database is forcing you to use highly de-normalized structures, then this is a different argument. You are constrained by the software and not the database, so you have no choices in the best database design.
So I think, the short answer is (in my opinion) database design is just as important with spatial databases as with traditional databases.
Michal,
Based on the additional information in your comment, there are a few things you could do. One thing to remember is that flexibility is key. If you put all of your polygon features in one table and all of your point features in another table, it makes it very difficult to compare different features. For example, if you have a polygon layer that contains both countries and counties within each country, it then becomes a challenge, and performance drag, to select all of the counties within a particular country. You are trying to run a query all on one layer, which doesn't take advantage of the flexibility of a relational database. It would be much better to let each type of feature reside in its own layer, as @mhoran_psprep said in his answer.
Also, since you have the flexibility to design the database, it might be worth having multiple geometry types for certain data types. For example, having a city represented as a polygon would be useful to perform spatial queries to determine all points of interest within that city. At the same time, it is oftentimes more desirable to represent a city as a point on a map. The database allows you to do this by separating the attribute data from the features into their own table, and linking them together with a Primary Key/Foreign Key structure. Briefly, a Primary Key is an identifier for a record in a table. A Foreign Key is a reference to that same identifier in a different table, thus linking them together.
Here is the structure I would use, part of which you actually described above.
Table
Continent - Fields - Geom (Polygon), ID, Name, Etc
Country - Fields - Geom (Polygon), ID, Name, Addtnl Attributes
Region - If there are different levels of regions within a country, then I would have them as different layers. So, if a country has regions called districts, and a number of districts combine to form a state, which then combine to form the country, you would have a layer for each type.
Reg_State - Fields - Geom (Polygon), ID (Primary Key), Name, Country ID, Etc
Reg_District - Fields - Geom (Polygon), ID, Name, Reg_State ID (Foreign Key), Etc
Cities - Have a table that contains attributes for each city, but no geometry. Have geometry tables with different geometries, like point or polygon, that link to the main city table.
City_Info - Fields - CityID (Primary key), Name, Population, Reg_District ID (Foreign Key), Etc, Etc
City_Pt - Fields - Geom (Point), ID, Name, CityID (Foreign Key)
City_Poly - Fields - Geom (Polygon), ID, Name, CityID (Foreign Key)
You can take these layers down to as fine a detail as you feel is necessary within the city, using neighborhoods, or addressing regions, whatever makes the most sense. Then, since this is for tourist information, you may want to include layers that contain points of interest.
PointofInterest - Fields - Geom (Point), ID, Name, CityID (Foreign Key), Description, Etc
The next set of tables will cover the tourist information that you are collecting. Again, this will depend on the amount and type of data. If you expect to have only a few entries about any particular regional level, it may make sense to have an overall table with fields to enter a region key, or country, or city. For example:
Tourist_Info - Fields - ID, Description, Country ID (Foreign Key), Reg_State ID (Foreign Key), Reg_District ID (Foreign Key), CityID (Foreign Key), Other_Info, Etc Etc
On the other hand, if you expect to have a large number of records for each region type, then it makes sense to have individual tourist info tables for each region. For example:
Tourist_Info_Country - Fields - ID, Description, Country ID (Foreign Key), Type, Etc, Etc
Tourist_Info_City - Fields - ID, Description, CityID (Foreign Key), Type, Etc, Etc
This is only one way of setting up the database, again, it depends on your specific needs for being able to link different pieces of data together. Once the table structure is determined, the next part will be to set up queries to retrieve the information you want, for inclusion in a report or some sort of viewing application.
I hope this provides at least an idea of where to start. Good luck!
Best Answer
Unfortunately, some of those feature are deeply dependent of the piece of software you use.
For example, let's take the U-Turns in ArcGIS and PGRouting. In ArcGIS, it is something you can choose as an option, in PGRouting, it is nested in the code.
On way street can be processed in two way. The are set up part of the graph with a very high cost (PGRouting) or simply removed from the graph (ESRI, which makes more sense)
Turns cost of prohibition are anyway a set of rules you need to know and that are really data dependent. Rule could be modified dynamically in PGRouting, but not in ArcGIS as far as I know (my last use of Network Analyst was on 9.3 and at that point, the graph had to be recompiled every time you make a change).
So you should take a look at what is done on both PGRouting and ArcGIS. It will determine a lot of how you need to store things.
http://support.esri.com/en/downloads/datamodel/detail/14 http://www.pgrouting.org/documentation.html