If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A simple Architectural Question !!
With the help of Allen Brown we've come up with this idea.
Here's a simpler suggestion that identifies country, state/province + country, and city + state/province + country: tblCountry *CountryID* Text abbreviated name Country Text full name. tblState *StateID* Text abbreviated name *CountryID* Text f.k. to tblCountry.CountryID. State Text full name. tblCity *CityID* AutoNum primary key City Text StateID Text ) f.k. to tblState CountryID Text ) Primary keys are marked with asterisks. I've suggested using natural keys for the first 2 tables rather than autonumbers. An artificial key seems like unnecessary overhead to me, and this also solves the interface issue of the disappearing values when the bound column is hidden and you filter your combos. You could a natural key in the 3rd table as well: City + StateID + CountryID. That would work well, you may well *want* that combination in your related tables. However the 3-field key can start to get unweildy if you then have other tables related off that that become 4- and 5-field keys. So, while you certainly want a unique index on the combination of those 3 fields, I've suggested the artificial key as I imagine that all sales data end up relating back to the CityID at some point. Question IS ? ... Do i really need an country ID since there is a known 255* or so limit and by default no two countries will EVER EVER have the same name, do I reallly need to have CountryID, won't Country alone suffice... The reason I ask leads to my second question, When in my other tables i create fields for let's say Country or province my programmer wants me to have the lookup store the names not the IDs, is that correct, and if I do it that way won't all my reports show country 33 for Canada vs showing the actual name of canada. What should I be storing in my lookups fields for each of Country Province City in bout 10 tables the ID "33" or the name "quebec" for example... Or... should I be leaving these fields as text in the tables and use lookups only in my forms for said fields of Country State and City which I find in about 10 or 15 tables and subsequent forms... While a little longwinded I just wanted to get my question across.. Thanks for all upcoming answers. Bernard Piette |
#2
|
|||
|
|||
A simple Architectural Question !!
No. You don't have to have both a CountryID field and a Country field if you
want to. You could just use the full name of the country as the CountryID (Text-type primary key field.) The problem with that approach is that the full name of some countries do not make good choices for drop-down list selection: long names, names starting with The, and names such as "People's Republic of China" (the technically correct name for Taiwan.) I therefore suggested that you use 2 *text* fields in tblCountry: - an abbreviated name as primary key (Text, not AutoNum), and - the full name of the country. The table would then contain records like this: CountryID Country ------------- ---------- Congo Democratic Republic of the Congo Taiwan People's Republic of China (Taiwan) USA United States of America -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bernard Piette" wrote in message ... With the help of Allen Brown we've come up with this idea. Here's a simpler suggestion that identifies country, state/province + country, and city + state/province + country: tblCountry *CountryID* Text abbreviated name Country Text full name. tblState *StateID* Text abbreviated name *CountryID* Text f.k. to tblCountry.CountryID. State Text full name. tblCity *CityID* AutoNum primary key City Text StateID Text ) f.k. to tblState CountryID Text ) Primary keys are marked with asterisks. I've suggested using natural keys for the first 2 tables rather than autonumbers. An artificial key seems like unnecessary overhead to me, and this also solves the interface issue of the disappearing values when the bound column is hidden and you filter your combos. You could a natural key in the 3rd table as well: City + StateID + CountryID. That would work well, you may well *want* that combination in your related tables. However the 3-field key can start to get unweildy if you then have other tables related off that that become 4- and 5-field keys. So, while you certainly want a unique index on the combination of those 3 fields, I've suggested the artificial key as I imagine that all sales data end up relating back to the CityID at some point. Question IS ? ... Do i really need an country ID since there is a known 255* or so limit and by default no two countries will EVER EVER have the same name, do I reallly need to have CountryID, won't Country alone suffice... The reason I ask leads to my second question, When in my other tables i create fields for let's say Country or province my programmer wants me to have the lookup store the names not the IDs, is that correct, and if I do it that way won't all my reports show country 33 for Canada vs showing the actual name of canada. What should I be storing in my lookups fields for each of Country Province City in bout 10 tables the ID "33" or the name "quebec" for example... Or... should I be leaving these fields as text in the tables and use lookups only in my forms for said fields of Country State and City which I find in about 10 or 15 tables and subsequent forms... While a little longwinded I just wanted to get my question across.. Thanks for all upcoming answers. Bernard Piette |
#3
|
|||
|
|||
A simple Architectural Question !!
Hi allen,
Thanks for your prompt reply. Yup, that makes sense to me for Country it's resolved I will follow that logic but here's a note from utteraccess and wondered what you thought about it... " I recommend you use Autonumbers for your PK in all your tables ... The Text PK will not Index as fast as a Numeric value ... I can not agree with your statement .. "An artificial key seems like unnecessary overhead to me". Also I do not recommend composite Primary keys ... If you want the combination of the 3 fields City + StateID + CountryID to be unique .. then you need to make these 3 fields a compound (composite) Index .... I would add an Autonumber field in this table for the PK ... When using lookup tables .. you store the record ID from the record in the lookup table ... You can reference any information stored in the lookup table for the specific record by including the lookup table in a query with the correct relational join. RDH -------------------- Ricky Hicks Microsoft MVP Birmingham, Alabama USA " So what do you think? Also, I don't think I can use same models for province and or city because it is possible their would be doubles or do the dbl and triple keys take care of that. Also any opinion about lookup fields in tables are they as bad as everyone says?? Thanks a bunch, Bernard Piette "Allen Browne" wrote: No. You don't have to have both a CountryID field and a Country field if you want to. You could just use the full name of the country as the CountryID (Text-type primary key field.) The problem with that approach is that the full name of some countries do not make good choices for drop-down list selection: long names, names starting with The, and names such as "People's Republic of China" (the technically correct name for Taiwan.) I therefore suggested that you use 2 *text* fields in tblCountry: - an abbreviated name as primary key (Text, not AutoNum), and - the full name of the country. The table would then contain records like this: CountryID Country ------------- ---------- Congo Democratic Republic of the Congo Taiwan People's Republic of China (Taiwan) USA United States of America -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bernard Piette" wrote in message ... With the help of Allen Brown we've come up with this idea. Here's a simpler suggestion that identifies country, state/province + country, and city + state/province + country: tblCountry *CountryID* Text abbreviated name Country Text full name. tblState *StateID* Text abbreviated name *CountryID* Text f.k. to tblCountry.CountryID. State Text full name. tblCity *CityID* AutoNum primary key City Text StateID Text ) f.k. to tblState CountryID Text ) Primary keys are marked with asterisks. I've suggested using natural keys for the first 2 tables rather than autonumbers. An artificial key seems like unnecessary overhead to me, and this also solves the interface issue of the disappearing values when the bound column is hidden and you filter your combos. You could a natural key in the 3rd table as well: City + StateID + CountryID. That would work well, you may well *want* that combination in your related tables. However the 3-field key can start to get unweildy if you then have other tables related off that that become 4- and 5-field keys. So, while you certainly want a unique index on the combination of those 3 fields, I've suggested the artificial key as I imagine that all sales data end up relating back to the CityID at some point. Question IS ? ... Do i really need an country ID since there is a known 255* or so limit and by default no two countries will EVER EVER have the same name, do I reallly need to have CountryID, won't Country alone suffice... The reason I ask leads to my second question, When in my other tables i create fields for let's say Country or province my programmer wants me to have the lookup store the names not the IDs, is that correct, and if I do it that way won't all my reports show country 33 for Canada vs showing the actual name of canada. What should I be storing in my lookups fields for each of Country Province City in bout 10 tables the ID "33" or the name "quebec" for example... Or... should I be leaving these fields as text in the tables and use lookups only in my forms for said fields of Country State and City which I find in about 10 or 15 tables and subsequent forms... While a little longwinded I just wanted to get my question across.. Thanks for all upcoming answers. Bernard Piette |
#4
|
|||
|
|||
A simple Architectural Question !!
You will certainly find people who hate AutoNumbers (the "Always use a
natural key" group, and the "AutoNumbers are unreliable in JET 4" group), and others who *only* use AutoNumbers (to be consistent (esp with WHERE conditions that involve a key value), and the performance argument.) There is no doubt that string operations are slower than numeric operations at the CPU level. But for most database operations, the IO is the bottleneck not the CPU, so the argument is rather academic. There's another argument that text-keys use more disk space. At the cost if disks, that's a non-issue. The increased size does increase the amount of IO, so you want to keep your text keys short. 24 characters is generally enough. But instead of theorizing about it, try it. Run some timing tests. You will see the performance issue is academic rather than real. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bernard Piette" wrote in message ... Hi allen, Thanks for your prompt reply. Yup, that makes sense to me for Country it's resolved I will follow that logic but here's a note from utteraccess and wondered what you thought about it... " I recommend you use Autonumbers for your PK in all your tables ... The Text PK will not Index as fast as a Numeric value ... I can not agree with your statement .. "An artificial key seems like unnecessary overhead to me". Also I do not recommend composite Primary keys ... If you want the combination of the 3 fields City + StateID + CountryID to be unique .. then you need to make these 3 fields a compound (composite) Index ... I would add an Autonumber field in this table for the PK ... When using lookup tables .. you store the record ID from the record in the lookup table ... You can reference any information stored in the lookup table for the specific record by including the lookup table in a query with the correct relational join. RDH -------------------- Ricky Hicks Microsoft MVP Birmingham, Alabama USA " So what do you think? Also, I don't think I can use same models for province and or city because it is possible their would be doubles or do the dbl and triple keys take care of that. Also any opinion about lookup fields in tables are they as bad as everyone says?? Thanks a bunch, Bernard Piette "Allen Browne" wrote: No. You don't have to have both a CountryID field and a Country field if you want to. You could just use the full name of the country as the CountryID (Text-type primary key field.) The problem with that approach is that the full name of some countries do not make good choices for drop-down list selection: long names, names starting with The, and names such as "People's Republic of China" (the technically correct name for Taiwan.) I therefore suggested that you use 2 *text* fields in tblCountry: - an abbreviated name as primary key (Text, not AutoNum), and - the full name of the country. The table would then contain records like this: CountryID Country ------------- ---------- Congo Democratic Republic of the Congo Taiwan People's Republic of China (Taiwan) USA United States of America -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bernard Piette" wrote in message ... With the help of Allen Brown we've come up with this idea. Here's a simpler suggestion that identifies country, state/province + country, and city + state/province + country: tblCountry *CountryID* Text abbreviated name Country Text full name. tblState *StateID* Text abbreviated name *CountryID* Text f.k. to tblCountry.CountryID. State Text full name. tblCity *CityID* AutoNum primary key City Text StateID Text ) f.k. to tblState CountryID Text ) Primary keys are marked with asterisks. I've suggested using natural keys for the first 2 tables rather than autonumbers. An artificial key seems like unnecessary overhead to me, and this also solves the interface issue of the disappearing values when the bound column is hidden and you filter your combos. You could a natural key in the 3rd table as well: City + StateID + CountryID. That would work well, you may well *want* that combination in your related tables. However the 3-field key can start to get unweildy if you then have other tables related off that that become 4- and 5-field keys. So, while you certainly want a unique index on the combination of those 3 fields, I've suggested the artificial key as I imagine that all sales data end up relating back to the CityID at some point. Question IS ? ... Do i really need an country ID since there is a known 255* or so limit and by default no two countries will EVER EVER have the same name, do I reallly need to have CountryID, won't Country alone suffice... The reason I ask leads to my second question, When in my other tables i create fields for let's say Country or province my programmer wants me to have the lookup store the names not the IDs, is that correct, and if I do it that way won't all my reports show country 33 for Canada vs showing the actual name of canada. What should I be storing in my lookups fields for each of Country Province City in bout 10 tables the ID "33" or the name "quebec" for example... Or... should I be leaving these fields as text in the tables and use lookups only in my forms for said fields of Country State and City which I find in about 10 or 15 tables and subsequent forms... While a little longwinded I just wanted to get my question across.. Thanks for all upcoming answers. Bernard Piette |
#5
|
|||
|
|||
A simple Architectural Question !!
Well that's it I'll go a mix of both natural and autonumber primary keys
according to the best use case scenario for each table. You will find the following to be my understanding of your recommedations. Allen you said try it out so here it is. Did I follow correctly ? C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine December 21, 2005 v2.mdb Table: tblAddresses Page: 1 Columns Name Type Size AddressID Long Integer 4 CountryName Text 50 ProvinceOrStateName Text 50 CityName Long Integer 4 PostalCode Long Integer 4 StreetAddress Text 50 AddressName Text 50 Relationships tblPostalCodestblAddresses tblPostalCodes tblAddresses PostalCodeID PostalCode Attributes: Not Enforced RelationshipType: One-To-Many tblCitiestblAddresses tblCities tblAddresses CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many tblProvinceOrStatetblAddresses tblProvinceOrState tblAddresses ProvinceOrStateName ProvinceOrStateName Attributes: Not Enforced RelationshipType: Indeterminate tblCountriestblAddresses tblCountries tblAddresses CountryName CountryName Attributes: Not Enforced RelationshipType: One-To-Many Table: tblCities Page: 3 Columns Name Type Size CountryName Text 50 ProvinceOrStateName Text 50 CityName Text 50 CityID Long Integer 4 Relationships tblCitiestblLocations tblCities tblLocations CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many tblCitiestblBuildings tblCities tblBuildings CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many tblCitiestblUnitPromisetoPurchase tblCities tblUnitPromisetoPurc CityID SignatureCity Attributes: Not Enforced RelationshipType: One-To-Many tblCitiestblAddresses tblCities tblAddresses CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many Table: tblCities Page: 4 tblProvinceOrStatetblCities tblProvinceOrState tblCities ProvinceOrStateName ProvinceOrStateName Attributes: Not Enforced RelationshipType: Indeterminate Table: tblCountries Page: 5 Columns Name Type Size CountryName Text 50 CountryOfficialName Text 50 CountryAbbrev Text 2 CountryDomainExt Text 3 Relationships tblCountriestblProvinceOrState tblCountries tblProvinceOrState CountryName CountryName Attributes: Not Enforced RelationshipType: One-To-Many tblCountriestblAddresses tblCountries tblAddresses CountryName CountryName Attributes: Not Enforced RelationshipType: One-To-Many Table: tblLocations Page: 6 Columns Name Type Size LocationID Long Integer 4 LocationName Text 50 CountryName Text 50 ProvinceOrStateName Text 50 CityName Long Integer 4 StreetAddress Text 50 Relationships tblCitiestblLocations tblCities tblLocations CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many Table: tblPostalCodes Page: 7 Columns Name Type Size PostalCodeID Long Integer 4 PostalCode Text 8 Relationships tblPostalCodestblAddresses tblPostalCodes tblAddresses PostalCodeID PostalCode Attributes: Not Enforced RelationshipType: One-To-Many Table: tblProvinceOrState Page: 8 Columns Name Type Size CountryName Text 50 ProvinceOrStateName Text 50 ProvinceAbbrev Text 3 Relationships tblCountriestblProvinceOrState tblCountries tblProvinceOrState CountryName CountryName Attributes: Not Enforced RelationshipType: One-To-Many tblProvinceOrStatetblAddresses tblProvinceOrState tblAddresses ProvinceOrStateName ProvinceOrStateName Attributes: Not Enforced RelationshipType: Indeterminate tblProvinceOrStatetblCities tblProvinceOrState tblCities ProvinceOrStateName ProvinceOrStateName Attributes: Not Enforced RelationshipType: Indeterminate Thaks Again, Bernard Piette |
#6
|
|||
|
|||
A simple Architectural Question !!
Bernard, I didn't go through that in detail, but the general idea seems on
track other than the choice not to use enforced relations. Relationships should almost always have enforced referential integrity, and in most cases the foreign key field should also have its Required property set to Yes. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Bernard Piette" wrote in message ... Well that's it I'll go a mix of both natural and autonumber primary keys according to the best use case scenario for each table. You will find the following to be my understanding of your recommedations. Allen you said try it out so here it is. Did I follow correctly ? C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine December 21, 2005 v2.mdb Table: tblAddresses Page: 1 Columns Name Type Size AddressID Long Integer 4 CountryName Text 50 ProvinceOrStateName Text 50 CityName Long Integer 4 PostalCode Long Integer 4 StreetAddress Text 50 AddressName Text 50 Relationships tblPostalCodestblAddresses tblPostalCodes tblAddresses PostalCodeID PostalCode Attributes: Not Enforced RelationshipType: One-To-Many tblCitiestblAddresses tblCities tblAddresses CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many tblProvinceOrStatetblAddresses tblProvinceOrState tblAddresses ProvinceOrStateName ProvinceOrStateName Attributes: Not Enforced RelationshipType: Indeterminate tblCountriestblAddresses tblCountries tblAddresses CountryName CountryName Attributes: Not Enforced RelationshipType: One-To-Many Table: tblCities Page: 3 Columns Name Type Size CountryName Text 50 ProvinceOrStateName Text 50 CityName Text 50 CityID Long Integer 4 Relationships tblCitiestblLocations tblCities tblLocations CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many tblCitiestblBuildings tblCities tblBuildings CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many tblCitiestblUnitPromisetoPurchase tblCities tblUnitPromisetoPurc CityID SignatureCity Attributes: Not Enforced RelationshipType: One-To-Many tblCitiestblAddresses tblCities tblAddresses CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many Table: tblCities Page: 4 tblProvinceOrStatetblCities tblProvinceOrState tblCities ProvinceOrStateName ProvinceOrStateName Attributes: Not Enforced RelationshipType: Indeterminate Table: tblCountries Page: 5 Columns Name Type Size CountryName Text 50 CountryOfficialName Text 50 CountryAbbrev Text 2 CountryDomainExt Text 3 Relationships tblCountriestblProvinceOrState tblCountries tblProvinceOrState CountryName CountryName Attributes: Not Enforced RelationshipType: One-To-Many tblCountriestblAddresses tblCountries tblAddresses CountryName CountryName Attributes: Not Enforced RelationshipType: One-To-Many Table: tblLocations Page: 6 Columns Name Type Size LocationID Long Integer 4 LocationName Text 50 CountryName Text 50 ProvinceOrStateName Text 50 CityName Long Integer 4 StreetAddress Text 50 Relationships tblCitiestblLocations tblCities tblLocations CityID CityName Attributes: Not Enforced RelationshipType: One-To-Many Table: tblPostalCodes Page: 7 Columns Name Type Size PostalCodeID Long Integer 4 PostalCode Text 8 Relationships tblPostalCodestblAddresses tblPostalCodes tblAddresses PostalCodeID PostalCode Attributes: Not Enforced RelationshipType: One-To-Many Table: tblProvinceOrState Page: 8 Columns Name Type Size CountryName Text 50 ProvinceOrStateName Text 50 ProvinceAbbrev Text 3 Relationships tblCountriestblProvinceOrState tblCountries tblProvinceOrState CountryName CountryName Attributes: Not Enforced RelationshipType: One-To-Many tblProvinceOrStatetblAddresses tblProvinceOrState tblAddresses ProvinceOrStateName ProvinceOrStateName Attributes: Not Enforced RelationshipType: Indeterminate tblProvinceOrStatetblCities tblProvinceOrState tblCities ProvinceOrStateName ProvinceOrStateName Attributes: Not Enforced RelationshipType: Indeterminate Thaks Again, Bernard Piette |
#7
|
|||
|
|||
A simple Architectural Question !!
I'm using a similar setup with states and coutries only. I don't use a city
lookup table which is what I use these for on forms only. tblState [StateID] * Number [State] Abbreviation [State Name] Full Name of state [CountryID] Number tblCountry [CountryID]* Number [Country Name] Full name of country Bernard Piette wrote: With the help of Allen Brown we've come up with this idea. Here's a simpler suggestion that identifies country, state/province + country, and city + state/province + country: tblCountry *CountryID* Text abbreviated name Country Text full name. tblState *StateID* Text abbreviated name *CountryID* Text f.k. to tblCountry.CountryID. State Text full name. tblCity *CityID* AutoNum primary key City Text StateID Text ) f.k. to tblState CountryID Text ) Primary keys are marked with asterisks. I've suggested using natural keys for the first 2 tables rather than autonumbers. An artificial key seems like unnecessary overhead to me, and this also solves the interface issue of the disappearing values when the bound column is hidden and you filter your combos. You could a natural key in the 3rd table as well: City + StateID + CountryID. That would work well, you may well *want* that combination in your related tables. However the 3-field key can start to get unweildy if you then have other tables related off that that become 4- and 5-field keys. So, while you certainly want a unique index on the combination of those 3 fields, I've suggested the artificial key as I imagine that all sales data end up relating back to the CityID at some point. Question IS ? ... Do i really need an country ID since there is a known 255* or so limit and by default no two countries will EVER EVER have the same name, do I reallly need to have CountryID, won't Country alone suffice... The reason I ask leads to my second question, When in my other tables i create fields for let's say Country or province my programmer wants me to have the lookup store the names not the IDs, is that correct, and if I do it that way won't all my reports show country 33 for Canada vs showing the actual name of canada. What should I be storing in my lookups fields for each of Country Province City in bout 10 tables the ID "33" or the name "quebec" for example... Or... should I be leaving these fields as text in the tables and use lookups only in my forms for said fields of Country State and City which I find in about 10 or 15 tables and subsequent forms... While a little longwinded I just wanted to get my question across.. Thanks for all upcoming answers. Bernard Piette -- Joe Cilinceon |
#8
|
|||
|
|||
A simple Architectural Question !!
Bernard Piette wrote: here's a note from utteraccess and wondered what you thought about it... "Also I do not recommend composite Primary keys" That would come as a surprise to hundreds of thousands of commercial database developers all over the world! There is absolutely nothing wrong with composite primary keys. They are the natural choice for child tables in parent/child relationships. It's nonsense to say that they have any bad performance impact. Of course, "everything in moderation". If I had 10 levels of 1:m relationships, I'd certainly be reluctant to have a 10-field PK in the bottom-most table! I'd probably introduce an autonumber PK, at some point in that structure. But it's rare to go anywhere near that deep, in my opionion. HTH, TC |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Simple Question - Driving me Crazy | David | Running & Setting Up Queries | 2 | May 19th, 2005 12:55 AM |
Survey Results | SAm | Running & Setting Up Queries | 10 | May 17th, 2005 08:32 PM |
Simple question: How to get web query to NOT overwrite data when it refreshes? | A Smith | General Discussion | 3 | September 6th, 2004 06:55 AM |
SIMPLE BEGINNER ACCESS 2000 QUESTION | Moni881 | New Users | 5 | July 15th, 2004 09:51 PM |
Simple Excel Question | Scott B. Hogle | Worksheet Functions | 3 | March 14th, 2004 11:53 PM |