A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

A simple Architectural Question !!



 
 
Thread Tools Display Modes
  #1  
Old December 21st, 2005, 05:42 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old December 21st, 2005, 06:04 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old December 21st, 2005, 07:03 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old December 21st, 2005, 07:23 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old December 21st, 2005, 08:31 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old December 21st, 2005, 09:31 AM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old December 21st, 2005, 12:44 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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  
Old December 21st, 2005, 02:26 PM posted to microsoft.public.access.tablesdbdesign
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 15th, 2004 12:53 AM


All times are GMT +1. The time now is 02:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.