Overall Analysis

I'm sure this has been asked and aswrered but I looked for hours....ok Here
goes.

So I'm building a real estate database managing condo sales and appartment
rentals.

I feel at the base should be solid locations info. But the budget is very
little a few gs and they want lots of queries and reports so I'm kissing this
keeping it simple and will surely not be able to offer 3nf. They don't want
to deal with regions so only country state city

tblCountries
CountryID PK Auto
CountryName
CountryAbbrev

tblProvinceOrState
ProvinceOrStateID PK Auto
ProvinceOrSateName
ProvinceAbbrev

tblCities
CitiesID PK Auto
CityName

tblLocations
LocationID PK
CountryName FK lookup tblCountries
ProvinceOrSateName FK lookup tblProvinceOrState
CityName FK lookup tblCities

The point is that all tables and forms will do lookups with combo boxes on
the fields in Locations table, if doesn't exist than user puts element in
either a new country state or city form so that data entry human errors can
be avoided at all costs by forcing the said country or province or city to
exist beforehand. As is the country provinces and cities drop down boxes are
not synchronized.

Looking at the concepts and I want to be certain b/c must do the same with
Properties Buildings and Units. Is this the right direction?? All help most
apreciated.

See my other post if you want to see most of the tables Thanks again.
Bernard Piette
Solutions Architect
http://www.BernardPiette.com
1610 Boul. St-Jospeh, Suite 1
Lachine, Quebec H8S 2N1
Tel: (514)577-7978

The structure you are suggest does not provide a way to specify what
states/provinces are valid for any given country. So when you select a city
in your 4th table, there would be nothing to stop me choosing Las Vages,
Ontario, France.

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.

HTH.
--
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
...
I'm sure this has been asked and aswrered but I looked for hours....ok
Here
goes.

So I'm building a real estate database managing condo sales and appartment
rentals.

I feel at the base should be solid locations info. But the budget is very
little a few gs and they want lots of queries and reports so I'm kissing
this
keeping it simple and will surely not be able to offer 3nf. They don't
want
to deal with regions so only country state city

tblCountries
CountryID PK Auto
CountryName
CountryAbbrev

tblProvinceOrState
ProvinceOrStateID PK Auto
ProvinceOrSateName
ProvinceAbbrev

tblCities
CitiesID PK Auto
CityName

tblLocations
LocationID PK
CountryName FK lookup tblCountries
ProvinceOrSateName FK lookup tblProvinceOrState
CityName FK lookup tblCities

The point is that all tables and forms will do lookups with combo boxes on
the fields in Locations table, if doesn't exist than user puts element in
either a new country state or city form so that data entry human errors
can
be avoided at all costs by forcing the said country or province or city to
exist beforehand. As is the country provinces and cities drop down boxes
are
not synchronized.

Looking at the concepts and I want to be certain b/c must do the same with
Properties Buildings and Units. Is this the right direction?? All help
most
apreciated.

See my other post if you want to see most of the tables Thanks again.
Bernard Piette
Solutions Architect
http://www.BernardPiette.com
1610 Boul. St-Jospeh, Suite 1
Lachine, Quebec H8S 2N1
Tel: (514)577-7978

On Sat, 3 Dec 2005 11:26:26 +0800, "Allen Browne"
wrote:

You could a natural key in the 3rd table as well: City + StateID +
CountryID.

Minor nitpick: There are two "cities" named Los Alamos in New Mexico.
One's the famous one, another is a tiny dot on the map north of Las
Vegas (the one in New Mexico, not the bigger one in Nevada).

I'm not sure how the Post Office copes... county name maybe... g

John W. Vinson[MVP]

"John Vinson" wrote in message
...
On Sat, 3 Dec 2005 11:26:26 +0800, "Allen Browne"
wrote:

You could a natural key in the 3rd table as well: City + StateID
+
CountryID.

Minor nitpick: There are two "cities" named Los Alamos in New
Mexico.
One's the famous one, another is a tiny dot on the map north of
Las
Vegas (the one in New Mexico, not the bigger one in Nevada).

I'm not sure how the Post Office copes... county name maybe... g

John W. Vinson[MVP]

John Vinson,

Zip code. The USPS relies heavily on the zip code.


Sincerely,

Chris O.

Thanks that is more towards the original. Going back there now :-)
--
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database


"Allen Browne" wrote:

The structure you are suggest does not provide a way to specify what
states/provinces are valid for any given country. So when you select a city
in your 4th table, there would be nothing to stop me choosing Las Vages,
Ontario, France.

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.

HTH.
--
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
...
I'm sure this has been asked and aswrered but I looked for hours....ok
Here
goes.

So I'm building a real estate database managing condo sales and appartment
rentals.

I feel at the base should be solid locations info. But the budget is very
little a few gs and they want lots of queries and reports so I'm kissing
this
keeping it simple and will surely not be able to offer 3nf. They don't
want
to deal with regions so only country state city

tblCountries
CountryID PK Auto
CountryName
CountryAbbrev

tblProvinceOrState
ProvinceOrStateID PK Auto
ProvinceOrSateName
ProvinceAbbrev

tblCities
CitiesID PK Auto
CityName

tblLocations
LocationID PK
CountryName FK lookup tblCountries
ProvinceOrSateName FK lookup tblProvinceOrState
CityName FK lookup tblCities

The point is that all tables and forms will do lookups with combo boxes on
the fields in Locations table, if doesn't exist than user puts element in
either a new country state or city form so that data entry human errors
can
be avoided at all costs by forcing the said country or province or city to
exist beforehand. As is the country provinces and cities drop down boxes
are
not synchronized.

Looking at the concepts and I want to be certain b/c must do the same with
Properties Buildings and Units. Is this the right direction?? All help
most
apreciated.

See my other post if you want to see most of the tables Thanks again.
Bernard Piette
Solutions Architect
http://www.BernardPiette.com
1610 Boul. St-Jospeh, Suite 1
Lachine, Quebec H8S 2N1
Tel: (514)577-7978

Agreed, might as well go all the way. !
--
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database


"John Vinson" wrote:

On Sat, 3 Dec 2005 11:26:26 +0800, "Allen Browne"
wrote:

You could a natural key in the 3rd table as well: City + StateID +
CountryID.

Minor nitpick: There are two "cities" named Los Alamos in New Mexico.
One's the famous one, another is a tiny dot on the map north of Las
Vegas (the one in New Mexico, not the bigger one in Nevada).

I'm not sure how the Post Office copes... county name maybe... g

John W. Vinson[MVP]

you are right, I most definetely feel a need for county but client doesn't
want to go through that for their needs. Good point though.
Thanks
--
Bernard


"Chris2" wrote:


"John Vinson" wrote in message
...
On Sat, 3 Dec 2005 11:26:26 +0800, "Allen Browne"
wrote:

You could a natural key in the 3rd table as well: City + StateID
+
CountryID.

Minor nitpick: There are two "cities" named Los Alamos in New
Mexico.
One's the famous one, another is a tiny dot on the map north of
Las
Vegas (the one in New Mexico, not the bigger one in Nevada).

I'm not sure how the Post Office copes... county name maybe... g

John W. Vinson[MVP]

John Vinson,

Zip code. The USPS relies heavily on the zip code.


Sincerely,

Chris O.