On 13/06/11 11:06, Brett Parker wrote:
On 13 Jun 10:39, Richard Parsons wrote:
Maybe, you need to re-think your proposed database structure. I'm no expert, but when you add data (like another Brewery Name) to a database you shouldn't need to add a new table. Have a look at this article which I think may help: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html
So, what I'd be looking at is:
Table : Brewery Columns: id name description Table : Beer Columns: id brewery_id name descriptionThus having a one to many relationship between brewery and beer, storing the id of the brewery in the beer means that you can change the fields of the brewery (in the event of it being renamed, or the description changing, etc), and that applies immediately to all beers of the brewery.
This is normal database normalisation.
I've created a couple of tables. This what they look like (csv output from the creator program) Brewery :- "ID","Name","Web_URL","Address"
ID is the 'TEXT_PRIMARY_KEY' and the rest of the fields are set as text.
Beers :- "ID","Beer_Name","Brewery_ID","ABV","Consumed_Yes_No","Where_Consumed","Tasting_Notes"
Again, ID is the 'TEXT_PRIMARY_KEY' and the rest of the fields are set as text. I wondered if I should have set Consumed_Yes_No as a BOOL element but I can't see how to do that in the program I'm using. Is SQL itself better at that or is it unnecessary?
So the first real question is, have I done that correctly? I wondered if I need an ID in the Beers table or could the Beer_Name be used as that? I realise though that lots of beers have the same name and the web site cautions against that.
Second question. Do I link the relationship between the tables in SQL code or in the program code that will make use of this data? I can see how to do the latter from this section of code :-
model->setTable("employee");
model->setRelation(2, QSqlRelation("city", "id", "name")); model->setRelation(3, QSqlRelation("country", "id", "name"));
The code is taken from one of the examples used here - http://doc.qt.nokia.com/4.7-snapshot/qsqlrelationaltablemodel.html