Re: Best practice on table design
Carsten, Thanks for the answer (and other thanks go to the other guys that answered me). I think normalization is the way to go. I think it is the right thing to do (in theory). The problem is that theory doesn't fit all. Basically I have some tables with only 2 fields (ID and name), and a central table, joined by a one-to-many relation. The key point here are the 2-field tables. If I keep them separate, I can extend them (add new fields) without problem when need arise. But if there is no need for an extension (my case), all I get is a greater number of tables that I have to take care of. Wouldn't be better (maybe more efficient ?) to put all the 2-field tables in only table, with a separate ENUM field to separate the records on categories ? -- Cip CRD Hi Ciprian, CRD OK, I'm by no means a DB guru, so a) take this with a grain of salt CRD and b) feel free to tear it apart if I'm completely wrong! ;] CRD If in fact your people and city tables aren't going to change very CRD often, then why don't you just go all the way and keep that CRD information somewhere else in your application and write it straight CRD to your travel_expenditures table, e.g.: [..] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Ciprian Trofin writes: Basically I have some tables with only 2 fields (ID and name), and a central table, joined by a one-to-many relation. The key point here are the 2-field tables. If I keep them separate, I can extend them (add new fields) without problem when need arise. But if there is no need for an extension (my case), all I get is a greater number of tables that I have to take care of. Wouldn't be better (maybe more efficient ?) to put all the 2-field tables in only table, with a separate ENUM field to separate the records on categories ? Ciprian, There are two main purposes for normalization in this case. The first is to provide consistency of data. Going back to your example, placing the city name in each record allows the possibility of multiple spellings for the city name, since each record has its own copy of the data. The second is space savings, since storing an int is usually 4 bytes at worst while a city name is definitely more than 4 bytes. Yes it does generate a second table that only has the mappings from cityID to cityName, but you will likely find it well worth the effort to use the mapping. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practice on table design
Cities (CityID, Name) People (PersonID, Name) Travel_Exp (ExpID, Date, PersonID, Per_Diem) Travel_Exp_Cities (CityID, ExpID) Based on the descriptions I'd tend to go with a normalized table set of this nature: Cities (CityID, Name) People (PersonID, Name) Travel_Exp (ExpID, Date, PersonID, CityID, Exp) This provides consistent use of person and city. Along with gathering related data into the same record. It is doubtful that an expense would reference more than one person or city. Normalizing to this table set provides a simple means of querying related data, without undue duplication of data elements with the possibility of errors creeping in during the data input. But these observations are based upon my own common sense view of the kinds of business rules/processes that are likely to be used. If your business processes would not follow the described mechanisms, say you do indeed share travel_expenses between individuals, or the expense can be across cities, the normalization I've described would not fit. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
Best practice on table design
Hello, I have the following structure: people - | id | name | - | 1 | John | | 2 | Mary | - cities | id | city| | 1 | Glasgow | | 2 | Madrid | | 3 | Berlin | travel_expenditures --- | id | date | id_people | id_city | per_diem | --- | 1 | 05.08 | 1 | 1 |1.600 | | 2 | 05.09 | 2 | 3 |2.000 | | 3 | 06.12 | 1 | 2 |1.000 | --- The `people` and `cities` tables aren't going to be very populated, so a thought to merge them into something like this: central_data --- | id | name| type | --- | 1 | John| P| | 2 | Glasgow | C| | 3 | Mary| P| | 4 | Madrid | C| | 5 | Berlin | C| --- where central_data.type is P for people and C for cities. Do you think it is a good ideea ? -- Best regards, Ciprian Trofin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
