My answer is probably not going to be very helpful! It depends on a lot of factors. If the database is likely to be hit heavily, you'll want to normalize the data strongly, and you might even create a glue table such as travel_expenditures_cities to avoid row duplication in the travel_expenditures table e.g.:
Cities (CityID, Name) People (PersonID, Name) Travel_Exp (ExpID, Date, PersonID, Per_Diem) Travel_Exp_Cities (CityID, ExpID) This makes your queries more complicated, but reduces the size of the database - joining isn't necessarily bad, and you may find that a more normalized database structure allows you to reduce possible contention. As is noted below, though, if your data will not change that often (e.g. you have 10 people and are unlikely to change this list), and you do not expect a massive volume of queries, all you achieve by normalization is a more complex database. You could have one table with all the data in it, although I would be inclined to use the original three you display, so that you have 'lookup lists' for people and cities which you can call upon elsewhere in your application. Cheers, Matt -----Original Message----- From: Carsten R. Dreesbach [mailto:[EMAIL PROTECTED] Sent: 09 April 2004 00:52 To: Ciprian Trofin Cc: [EMAIL PROTECTED] Subject: Re: Best practice on table design Hi Ciprian, OK, I'm by no means a DB guru, so a) take this with a grain of salt and b) feel free to tear it apart if I'm completely wrong! ;] If in fact your people and city tables aren't going to change very often, then why don't you just go all the way and keep that information somewhere else in your application and write it straight to your travel_expenditures table, e.g.: travel_expenditures ----------------------------------------------- | id | date | people | city | per_diem | ----------------------------------------------- | 1 | 05.08 | John | Glasgow | 1.600 | | 2 | 05.09 | Mary | Madrid | 2.000 | | 3 | 06.12 | John | Madrid | 1.000 | ----------------------------------------------- This way you completely avoid any JOINs. Of course, this only makes sense if your people and cities information is not likely to change much at all... Thursday, April 8, 2004, 3:29:22 AM, you wrote: CT> Hello, CT> I have the following structure: CT> people CT> ------------- CT> | id | name | CT> ------------- CT> | 1 | John | CT> | 2 | Mary | CT> ------------- CT> cities CT> ---------------- CT> | id | city | CT> ---------------- CT> | 1 | Glasgow | CT> | 2 | Madrid | CT> | 3 | Berlin | CT> ---------------- CT> travel_expenditures CT> ----------------------------------------------- CT> | id | date | id_people | id_city | per_diem | CT> ----------------------------------------------- CT> | 1 | 05.08 | 1 | 1 | 1.600 | CT> | 2 | 05.09 | 2 | 3 | 2.000 | CT> | 3 | 06.12 | 1 | 2 | 1.000 | CT> ----------------------------------------------- CT> The `people` and `cities` tables aren't going to be very populated, so a CT> thought to merge them into something like this: CT> central_data CT> ----------------------- CT> | id | name | type | CT> ----------------------- CT> | 1 | John | P | CT> | 2 | Glasgow | C | CT> | 3 | Mary | P | CT> | 4 | Madrid | C | CT> | 5 | Berlin | C | CT> ----------------------- CT> where central_data.type is P for people and C for cities. CT> Do you think it is a good ideea ? CT> -- CT> Best regards, CT> Ciprian Trofin -- Best regards, Carsten R. Dreesbach mailto:[EMAIL PROTECTED] Senior Consultant Systar, Inc. 8000 Westpark Dr Suite 450 McLean, VA 22102 USA Tel: (703) 556-8436 Fax: (703) 556-8430 Cel: (571) 213-7904 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
