Originally Posted by robbaralla
Hi there readers, does someone out there has a bit of experience with database structure?
Well, I have a bit of (about 20 years) experience with data modeling and logical database structures, so I'd like to comment on this problem.
Firstly, its important to recognise that fundamentally, a database structure is a deductive logic system. With a correctly designed database, each table represents one or more "Facts" that can be expressed in natural language.
Futhermore in a properly designed data structure, there are no duplications.
So for example, your table structure (the data model) might contain the following "Facts".
Photo was taken at Place
Photo was taken on Date
Place is in Country
Place is in City
From these four "Facts" you can see that there will be a "Photo" table and a "Place" table.
It is good data modeling practice to uniquely identify the things that the facts are about, so the foregoing four facts might be shown as follows:
Photo(id) was taken at Place(id)
Photo(id) was taken on Date(yymmdd)
Place(id) is in Country(id)
Place(id) is in City(name)
From this you may be able to see that the Photo(id) will be the primary key in the Photo table and the Place(id) will be the primary key in the Place table.
The PK-FK structures are "just" links between those columns that are serving as unique identifiers for the "fact instances" that are contained in each table.
Where all this leads to is that with all but the most simple data models, you can never know what your eventual table structure will look like until you have defined the facts.
So, even your simple example and your discussion with Imar shows that its easy to spend a lot of time fiddling about with table structures.
My suggestion is that you begin by listing the Facts that you want your database to store. After that, your table structure should be easier to see.
Hope this helps.