Table Design Review of Fields and Tables

[Guest]

TblUnits
Name Type Size

UnitID Long Integer 4 PK
PropertyName Text 50
BuildingName Text 50
UnitName Text 50
ManagementCompany Text 50
UnitCategory Text 50
UnitSize Text 50
UnitFloorNumber Text 10
UnitLocationInBuilding Text 25
ParkingID Long Integer 4
ParkingID2 Long Integer 4
NotaryID Long Integer 4
EvaluationReport Yes/No 1
FullInspectionReport Yes/No 1
PyriteTest Yes/No 1
Insured Yes/No 1
FaxPTPtoBrokerOrCaisse Yes/No 1
CopytoConsultant Yes/No 1
FaxtoNotaryOrMailwChq Yes/No 1
FaxLeasetoBankOrBroker Yes/No 1
NotarizedDate Date/Time 8
CommissionPaid Currency 8
IsLeased Yes/No 1
IsSold Yes/No 1
IsVacant Yes/No 1
IsReady Yes/No 1

tblUnitContactInfo
Name Type Size

PK FK UnitID Text 4
UnitMunicipalLotNumber Text 50
UnitManagerID Long Integer 4
UnitNotaryID Long Integer 4
UnitPhoneNumber Text 14
UnitFaxNumber Text 14
UnitEmailAddress Text 50
UnitWebsiteAddress Text 50
UnitShortDescription Text 255

tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

tblUnitFinancials
Name Type Size

PK FK UnitID Text 4
UnitMonthlyRentRevenue Currency 8
UnitMonthlyParkingRevenue Currency 8
UnitTotalMonthlyIncome Currency 8
Augmentation Currency 8
UnitMonthlyRentGuaranteed Currency 8
UnitMarketValue Currency 8
UnitParkingPurchasePrice Currency 8
UnitTotalSalePrice Currency 8
DiscountOnUnitTotalPrice Currency 8
UnitCashFlow Currency 8
UnitROIPercent Double 8
UnitRedexActualSoldPrice Currency 8

Other tables include UnitProjects, UnitTasks, UnitAssignments,
UnitMultimedia etc.

Some field sizes need to be adjusted. This is a small portion of the
database ofcourse.
Regular tables also include employees locations properties buyers etc. but
this should help clarify what I meant. These are in five tables should I
leave it or put all in same table so that I can use the tab control in my
forms easily and move the fields around at will .

I would be glad to include full documentation and or Visio Database Diagrams
if anyone is intertested in helping with the architectrure. Database includes
hundreds of fields. :-]

Bernard

 

[Duane Hookom]

I find this structure fairly un-normalized. Let's just look at this table:
tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

I would not store "date titles" in field names. For instance, what would you
do if you needed to add a date for "fire inspection"? I would create a table
of date types with records containing unique values like Acquired, Reserved,
Promise To Purchase, ...
tblDateTypes
===============
DateTypeID
DateType "Acquired",...
(you could even add a record for CopytoConsultant so you would know the
date. This would allow you to remove that field from tblUnits)


Then create a table of Unit Dates.

tblUnitDates
=================
UnitID
DateTypeID
CompletionDate
Comments

That's just a start but I trust you understand the normalizing objective...

--
Duane Hookom
MS Access MVP

TblUnits
Name Type Size

UnitID Long Integer 4 PK
PropertyName Text 50
BuildingName Text 50
UnitName Text 50
ManagementCompany Text 50
UnitCategory Text 50
UnitSize Text 50
UnitFloorNumber Text 10
UnitLocationInBuilding Text 25
ParkingID Long Integer 4
ParkingID2 Long Integer 4
NotaryID Long Integer 4
EvaluationReport Yes/No 1
FullInspectionReport Yes/No 1
PyriteTest Yes/No 1
Insured Yes/No 1
FaxPTPtoBrokerOrCaisse Yes/No 1
CopytoConsultant Yes/No 1
FaxtoNotaryOrMailwChq Yes/No 1
FaxLeasetoBankOrBroker Yes/No 1
NotarizedDate Date/Time 8
CommissionPaid Currency 8
IsLeased Yes/No 1
IsSold Yes/No 1
IsVacant Yes/No 1
IsReady Yes/No 1

tblUnitContactInfo
Name Type Size

PK FK UnitID Text 4
UnitMunicipalLotNumber Text 50
UnitManagerID Long Integer 4
UnitNotaryID Long Integer 4
UnitPhoneNumber Text 14
UnitFaxNumber Text 14
UnitEmailAddress Text 50
UnitWebsiteAddress Text 50
UnitShortDescription Text 255

tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

tblUnitFinancials
Name Type Size

PK FK UnitID Text 4
UnitMonthlyRentRevenue Currency 8
UnitMonthlyParkingRevenue Currency 8
UnitTotalMonthlyIncome Currency 8
Augmentation Currency 8
UnitMonthlyRentGuaranteed Currency 8
UnitMarketValue Currency 8
UnitParkingPurchasePrice Currency 8
UnitTotalSalePrice Currency 8
DiscountOnUnitTotalPrice Currency 8
UnitCashFlow Currency 8
UnitROIPercent Double 8
UnitRedexActualSoldPrice Currency 8

Other tables include UnitProjects, UnitTasks, UnitAssignments,
UnitMultimedia etc.

Some field sizes need to be adjusted. This is a small portion of the
database ofcourse.
Regular tables also include employees locations properties buyers etc. but
this should help clarify what I meant. These are in five tables should I
leave it or put all in same table so that I can use the tab control in my
forms easily and move the fields around at will .

I would be glad to include full documentation and or Visio Database
Diagrams
if anyone is intertested in helping with the architectrure. Database
includes
hundreds of fields. :-]

Bernard

 

[Guest]

Duane Thanks for your input.

to be sure right now there is only one date for each action.
So I agree actually that a copytoconsultantdate and others could be required.
Point being that if we know all the tasks we can plan the structure in
advance.

My question is the way you propose which seems better to me I'm a little
confused?

What we will see in the forms?

I guess what I'm saying is right now I can see all dates based on the FK PK
UnitID from tblUnits in datasheet view they are all on one line, wouldn't I
now have multiple rows for one Unit a record for each DateType ?

Note: I very much apreciate your input, I'm not arguing here, justy trying
to understand

I find this structure fairly un-normalized. Let's just look at this table:
tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

I would not store "date titles" in field names. For instance, what would you
do if you needed to add a date for "fire inspection"? I would create a table
of date types with records containing unique values like Acquired, Reserved,
Promise To Purchase, ...
tblDateTypes
===============
DateTypeID
DateType "Acquired",...
(you could even add a record for CopytoConsultant so you would know the
date. This would allow you to remove that field from tblUnits)


Then create a table of Unit Dates.

tblUnitDates
=================
UnitID
DateTypeID
CompletionDate
Comments

That's just a start but I trust you understand the normalizing objective...

--
Duane Hookom
MS Access MVP

TblUnits
Name Type Size

UnitID Long Integer 4 PK
PropertyName Text 50
BuildingName Text 50
UnitName Text 50
ManagementCompany Text 50
UnitCategory Text 50
UnitSize Text 50
UnitFloorNumber Text 10
UnitLocationInBuilding Text 25
ParkingID Long Integer 4
ParkingID2 Long Integer 4
NotaryID Long Integer 4
EvaluationReport Yes/No 1
FullInspectionReport Yes/No 1
PyriteTest Yes/No 1
Insured Yes/No 1
FaxPTPtoBrokerOrCaisse Yes/No 1
CopytoConsultant Yes/No 1
FaxtoNotaryOrMailwChq Yes/No 1
FaxLeasetoBankOrBroker Yes/No 1
NotarizedDate Date/Time 8
CommissionPaid Currency 8
IsLeased Yes/No 1
IsSold Yes/No 1
IsVacant Yes/No 1
IsReady Yes/No 1

tblUnitContactInfo
Name Type Size

PK FK UnitID Text 4
UnitMunicipalLotNumber Text 50
UnitManagerID Long Integer 4
UnitNotaryID Long Integer 4
UnitPhoneNumber Text 14
UnitFaxNumber Text 14
UnitEmailAddress Text 50
UnitWebsiteAddress Text 50
UnitShortDescription Text 255

tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

tblUnitFinancials
Name Type Size

PK FK UnitID Text 4
UnitMonthlyRentRevenue Currency 8
UnitMonthlyParkingRevenue Currency 8
UnitTotalMonthlyIncome Currency 8
Augmentation Currency 8
UnitMonthlyRentGuaranteed Currency 8
UnitMarketValue Currency 8
UnitParkingPurchasePrice Currency 8
UnitTotalSalePrice Currency 8
DiscountOnUnitTotalPrice Currency 8
UnitCashFlow Currency 8
UnitROIPercent Double 8
UnitRedexActualSoldPrice Currency 8

Other tables include UnitProjects, UnitTasks, UnitAssignments,
UnitMultimedia etc.

Some field sizes need to be adjusted. This is a small portion of the
database ofcourse.
Regular tables also include employees locations properties buyers etc. but
this should help clarify what I meant. These are in five tables should I
leave it or put all in same table so that I can use the tab control in my
forms easily and move the fields around at will .

I would be glad to include full documentation and or Visio Database
Diagrams
if anyone is intertested in helping with the architectrure. Database
includes
hundreds of fields. :-]

Bernard

 

[John Vinson]

On Mon, 19 Dec 2005 15:45:02 -0800, "Bernard Piette"

Your tables are NOT properly designed: across the board, you have
one-to-many relationships embedded into each record. You run a real
risk of hitting the 2000-character limit of data actually USED in a
table - you can easily create and even use a table with (say) fifty
50-byte text fields, but you'll get an error (with no easy getaround)
if you actually USE them all.

Some suggestions embedded below...

TblUnits
Name Type Size

UnitID Long Integer 4 PK
PropertyName Text 50
BuildingName Text 50

Will one Property contain many Buildings? many Units? If so, a table
of Properties and one of Buildings would seem to be a good idea.

UnitName Text 50
ManagementCompany Text 50
UnitCategory Text 50
UnitSize Text 50
UnitFloorNumber Text 10
UnitLocationInBuilding Text 25
ParkingID Long Integer 4
ParkingID2 Long Integer 4

Repeating field. Might some luxury unit have THREE parking sites? You
cannot accommodate this unit if so.

NotaryID Long Integer 4
EvaluationReport Yes/No 1
FullInspectionReport Yes/No 1
PyriteTest Yes/No 1
Insured Yes/No 1
FaxPTPtoBrokerOrCaisse Yes/No 1
CopytoConsultant Yes/No 1
FaxtoNotaryOrMailwChq Yes/No 1
FaxLeasetoBankOrBroker Yes/No 1

All of these are Events, related many-to-many to Units. Rather than
having data ("PyriteTest") in a fieldname consider having a Table of
all the types of events relevant to a unit, related one-to-many to a
UnitEvents table with fields for the UnitID, EventID, and (perhaps) a
date. This will allow you to add (say) an Asbestos Inspection or an
Electromagnetic Field Inspection should these become required, without
redesigning all your tables, forms, reports, queries, etc.

NotarizedDate Date/Time 8 <<< probably an event too
CommissionPaid Currency 8
IsLeased Yes/No 1
IsSold Yes/No 1
IsVacant Yes/No 1
IsReady Yes/No 1

These last four are possibly derived data - if you have a table of
Leases you can determine by looking at it whether a unit is leased or
not.

tblUnitContactInfo
Name Type Size

PK FK UnitID Text 4
UnitMunicipalLotNumber Text 50
UnitManagerID Long Integer 4
UnitNotaryID Long Integer 4
UnitPhoneNumber Text 14
UnitFaxNumber Text 14
UnitEmailAddress Text 50
UnitWebsiteAddress Text 50
UnitShortDescription Text 255

tblUnitDates

Even more so than above - these should be RECORDS in an events table,
not fields.

Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

tblUnitFinancials
Name Type Size

PK FK UnitID Text 4
UnitMonthlyRentRevenue Currency 8
UnitMonthlyParkingRevenue Currency 8
UnitTotalMonthlyIncome Currency 8
Augmentation Currency 8
UnitMonthlyRentGuaranteed Currency 8
UnitMarketValue Currency 8
UnitParkingPurchasePrice Currency 8
UnitTotalSalePrice Currency 8
DiscountOnUnitTotalPrice Currency 8
UnitCashFlow Currency 8
UnitROIPercent Double 8
UnitRedexActualSoldPrice Currency 8

Are any of these derivable by using calculations upon the other
fields? E.g. is Total Monthly Income the sum of rent and parking
revenue? If so, don't store them - calculate them.

Other tables include UnitProjects, UnitTasks, UnitAssignments,
UnitMultimedia etc.

UnitBuildingPhotograph OLE Object -
UnitDocument1 OLE Object -
UnitDocument2 OLE Object -
UnitDocument3 OLE Object -
UnitDocument4 OLE Object -
UnitDocument5 OLE Object -
UnitDocument6 OLE Object -
UnitDocument7 OLE Object -
UnitDocument8 OLE Object -
UnitDocument9 OLE Object -
UnitDocument10 OLE Object -
UnitDocument12 OLE Object -
UnitFloorPlanDesings OLE Object -
UnitFloorPlanDesings1 OLE Object -
UnitFloorPlanDesings2 OLE Object -
UnitFloorPlanDesings3 OLE Object -
UnitFloorPlanDesings4 OLE Object -
UnitFloorPlanDesings5 OLE Object -
UnitMapPointMaps OLE Object -
UnitMapPointMaps1 OLE Object -
UnitMapPointMaps2 OLE Object -
UnitMapPointMaps3 OLE Object -
UnitMapPointMaps4 OLE Object -
UnitMapPointMaps5 OLE Object -

REPEATING fields - and horrible ones, given Access' gross inefficiency
at handling image data! You should - must I'd say - have a one to many
relationship from the Units table to a Documents table, containing in
each record the UnitID and a Text or Hyperlink field *pointing* to an
external image or document.

Some field sizes need to be adjusted. This is a small portion of the
database ofcourse.
Regular tables also include employees locations properties buyers etc. but
this should help clarify what I meant. These are in five tables should I
leave it or put all in same table so that I can use the tab control in my
forms easily and move the fields around at will .

I would be glad to include full documentation and or Visio Database Diagrams
if anyone is intertested in helping with the architectrure. Database includes
hundreds of fields. :-]

Hundreds too many I fear...


John W. Vinson[MVP]

 

[Guest]

Thanks John Vinson for your help .

Please see my comments and questions on your suggestions :

JV:> Will one Property contain many Buildings? many Units? If so, a table

of Properties and one of Buildings would seem to be a good idea.

BP: Yes, it's done so I will remove references to building and properties..
in the Units tbl.

JV:>You run a real risk of hitting the 2000-character limit of data actually
USED in a

table - you can easily create and even use a table with (say) fifty
50-byte text fields, but you'll get an error (with no easy getaround)
if you actually USE them all.

BP: I did not know about the 2000-character limit of data actually USED in a
table,
to be sure... you are saying if 50 fields with 40 characters in one form
would crash the application?

JV:> >ParkingID Long Integer 4

Repeating field. Might some luxury unit have THREE parking sites? You
cannot accommodate this unit if so.

BP: Client has stated 2 max and has authorized said structure but I'd feel
safer to build for possible three.
Can you expand on how I am to resolve this...

JV: All of these are Events, related many-to-many to Units. Rather than

having data ("PyriteTest") in a fieldname consider having a Table of
all the types of events relevant to a unit, related one-to-many to a
UnitEvents table with fields for the UnitID, EventID, and (perhaps) a
date. This will allow you to add (say) an Asbestos Inspection or an
Electromagnetic Field Inspection should these become required, without
redesigning all your tables, forms, reports, queries, etc.

BP: This is The Missing Table !!! This is the proper structure!

JV:> These last four are possibly derived data - if you have a table of

Leases you can determine by looking at it whether a unit is leased or
not.,

BP: but client said he only wanted to see a checkbox " I don't care when or
how it was done I just want to a checkmark" ( for stuff like Inspections
etc.)was his response. I guess you'll say it doesn't matter what client said
but I should build the srtucture correctly and then just program my forms to
show client what he wants to see. ?

JV: Even more so than above - these should be RECORDS in an events table,

not fields.

BP: So I'll put a date field in the events table and will solve that

JV:> Are any of these derivable by using calculations upon the other

fields? E.g. is Total Monthly Income the sum of rent and parking
revenue? If so, don't store them - calculate them.

BP: We plan to derive the financial data from an linked excel spreadsheet. I
posted a question about this today.
Since yes there are many calculations in the spreadsheet we prefer to leavbe
there and just show results from the speadsheet into the linked fields in
access. Will this work?

JV:

REPEATING fields - and horrible ones, given Access' gross inefficiency
at handling image data! You should - must I'd say - have a one to many
relationship from the Units table to a Documents table, containing in
each record the UnitID and a Text or Hyperlink field *pointing* to an
external image or document.

BP: Security forced me to rename these so I agree about the image data but
no worries it actually is other type of ole documents and yes we will point
to external linked objects and not embed them


Again John Vinson a huge thanks, you provide an inestimable service to your
fellow ms addicts.
I am very very apreciative.

Bernard Piette

On Mon, 19 Dec 2005 15:45:02 -0800, "Bernard Piette"

Your tables are NOT properly designed: across the board, you have
one-to-many relationships embedded into each record. >
Some suggestions embedded below...

TblUnits
Name Type Size
UnitID Long Integer 4 PK
PropertyName Text 50
BuildingName Text 50

UnitName Text 50
ManagementCompany Text 50
UnitCategory Text 50
UnitSize Text 50
UnitFloorNumber Text 10
UnitLocationInBuilding Text 25
ParkingID Long Integer 4
ParkingID2 Long Integer 4

Repeating field. Might some luxury unit have THREE parking sites? You
cannot accommodate this unit if so.

NotaryID Long Integer 4
EvaluationReport Yes/No 1
FullInspectionReport Yes/No 1
PyriteTest Yes/No 1
Insured Yes/No 1
FaxPTPtoBrokerOrCaisse Yes/No 1
CopytoConsultant Yes/No 1
FaxtoNotaryOrMailwChq Yes/No 1
FaxLeasetoBankOrBroker Yes/No 1

All of these are Events, related many-to-many to Units. Rather than
having data ("PyriteTest") in a fieldname consider having a Table of
all the types of events relevant to a unit, related one-to-many to a
UnitEvents table with fields for the UnitID, EventID, and (perhaps) a
date. This will allow you to add (say) an Asbestos Inspection or an
Electromagnetic Field Inspection should these become required, without
redesigning all your tables, forms, reports, queries, etc.

NotarizedDate Date/Time 8 <<< probably an event too
CommissionPaid Currency 8
IsLeased Yes/No 1
IsSold Yes/No 1
IsVacant Yes/No 1
IsReady Yes/No 1

These last four are possibly derived data - if you have a table of
Leases you can determine by looking at it whether a unit is leased or
not.

tblUnitContactInfo
Name Type Size

PK FK UnitID Text 4
UnitMunicipalLotNumber Text 50
UnitManagerID Long Integer 4
UnitNotaryID Long Integer 4
UnitPhoneNumber Text 14
UnitFaxNumber Text 14
UnitEmailAddress Text 50
UnitWebsiteAddress Text 50
UnitShortDescription Text 255

tblUnitDates


Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

tblUnitFinancials
Name Type Size

PK FK UnitID Text 4
UnitMonthlyRentRevenue Currency 8
UnitMonthlyParkingRevenue Currency 8
UnitTotalMonthlyIncome Currency 8
Augmentation Currency 8
UnitMonthlyRentGuaranteed Currency 8
UnitMarketValue Currency 8
UnitParkingPurchasePrice Currency 8
UnitTotalSalePrice Currency 8
DiscountOnUnitTotalPrice Currency 8
UnitCashFlow Currency 8
UnitROIPercent Double 8
UnitRedexActualSoldPrice Currency 8

Other tables include UnitProjects, UnitTasks, UnitAssignments,
UnitMultimedia etc.

UnitBuildingPhotograph OLE Object -
UnitDocument1 OLE Object -
UnitDocument2 OLE Object -
UnitDocument3 OLE Object -
UnitDocument4 OLE Object -
UnitDocument5 OLE Object -
UnitDocument6 OLE Object -
UnitDocument7 OLE Object -
UnitDocument8 OLE Object -
UnitDocument9 OLE Object -
UnitDocument10 OLE Object -
UnitDocument12 OLE Object -
UnitFloorPlanDesings OLE Object -
UnitFloorPlanDesings1 OLE Object -
UnitFloorPlanDesings2 OLE Object -
UnitFloorPlanDesings3 OLE Object -
UnitFloorPlanDesings4 OLE Object -
UnitFloorPlanDesings5 OLE Object -
UnitMapPointMaps OLE Object -
UnitMapPointMaps1 OLE Object -
UnitMapPointMaps2 OLE Object -
UnitMapPointMaps3 OLE Object -
UnitMapPointMaps4 OLE Object -
UnitMapPointMaps5 OLE Object -

Some field sizes need to be adjusted. This is a small portion of the
database ofcourse.
Regular tables also include employees locations properties buyers etc. but
this should help clarify what I meant. These are in five tables should I
leave it or put all in same table so that I can use the tab control in my
forms easily and move the fields around at will .

I would be glad to include full documentation and or Visio Database Diagrams
if anyone is intertested in helping with the architectrure. Database includes
hundreds of fields. :-]

Hundreds too many I fear...


John W. Vinson[MVP]

 

[TC]

JV:>You run a real risk of hitting the 2000-character limit of data actually
USED in a

BP: I did not know about the 2000-character limit of data actually USED in a table,

He meant in a /record/, not in a /table/. A single record can only
contain a maximum of 2kb in Jet 3 (Access 97 & before), or 4kb in Jet 4
(Access 2000 & later). Memo fields only count for 10(?) bytes, since
they are stored as pointers to the actual memo data which is stored
elsewhere within the file.

HTH,
TC

 

[Duane Hookom]

I would not base my table structure on how a customer wanted to view the
data on a form or report. There is very little justification for creating a
table structure that doesn't allow for reasonable flexibility in the future.

--
Duane Hookom
MS Access MVP
--

Duane Thanks for your input.

to be sure right now there is only one date for each action.
So I agree actually that a copytoconsultantdate and others could be
required.
Point being that if we know all the tasks we can plan the structure in
advance.

My question is the way you propose which seems better to me I'm a little
confused?

What we will see in the forms?

I guess what I'm saying is right now I can see all dates based on the FK
PK
UnitID from tblUnits in datasheet view they are all on one line, wouldn't
I
now have multiple rows for one Unit a record for each DateType ?

Note: I very much apreciate your input, I'm not arguing here, justy trying
to understand

I find this structure fairly un-normalized. Let's just look at this
table:
tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

I would not store "date titles" in field names. For instance, what would
you
do if you needed to add a date for "fire inspection"? I would create a
table
of date types with records containing unique values like Acquired,
Reserved,
Promise To Purchase, ...
tblDateTypes
===============
DateTypeID
DateType "Acquired",...
(you could even add a record for CopytoConsultant so you would know the
date. This would allow you to remove that field from tblUnits)


Then create a table of Unit Dates.

tblUnitDates
=================
UnitID
DateTypeID
CompletionDate
Comments

That's just a start but I trust you understand the normalizing
objective...

--
Duane Hookom
MS Access MVP

TblUnits
Name Type Size

UnitID Long Integer 4 PK
PropertyName Text 50
BuildingName Text 50
UnitName Text 50
ManagementCompany Text 50
UnitCategory Text 50
UnitSize Text 50
UnitFloorNumber Text 10
UnitLocationInBuilding Text 25
ParkingID Long Integer 4
ParkingID2 Long Integer 4
NotaryID Long Integer 4
EvaluationReport Yes/No 1
FullInspectionReport Yes/No 1
PyriteTest Yes/No 1
Insured Yes/No 1
FaxPTPtoBrokerOrCaisse Yes/No 1
CopytoConsultant Yes/No 1
FaxtoNotaryOrMailwChq Yes/No 1
FaxLeasetoBankOrBroker Yes/No 1
NotarizedDate Date/Time 8
CommissionPaid Currency 8
IsLeased Yes/No 1
IsSold Yes/No 1
IsVacant Yes/No 1
IsReady Yes/No 1

tblUnitContactInfo
Name Type Size

PK FK UnitID Text 4
UnitMunicipalLotNumber Text 50
UnitManagerID Long Integer 4
UnitNotaryID Long Integer 4
UnitPhoneNumber Text 14
UnitFaxNumber Text 14
UnitEmailAddress Text 50
UnitWebsiteAddress Text 50
UnitShortDescription Text 255

tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

tblUnitFinancials
Name Type Size

PK FK UnitID Text 4
UnitMonthlyRentRevenue Currency 8
UnitMonthlyParkingRevenue Currency 8
UnitTotalMonthlyIncome Currency 8
Augmentation Currency 8
UnitMonthlyRentGuaranteed Currency 8
UnitMarketValue Currency 8
UnitParkingPurchasePrice Currency 8
UnitTotalSalePrice Currency 8
DiscountOnUnitTotalPrice Currency 8
UnitCashFlow Currency 8
UnitROIPercent Double 8
UnitRedexActualSoldPrice Currency 8

Other tables include UnitProjects, UnitTasks, UnitAssignments,
UnitMultimedia etc.

Some field sizes need to be adjusted. This is a small portion of the
database ofcourse.
Regular tables also include employees locations properties buyers etc.
but
this should help clarify what I meant. These are in five tables should
I
leave it or put all in same table so that I can use the tab control in
my
forms easily and move the fields around at will .

I would be glad to include full documentation and or Visio Database
Diagrams
if anyone is intertested in helping with the architectrure. Database
includes
hundreds of fields. :-]

Bernard

 

[John Vinson]

Please see my comments and questions on your suggestions :

JV:> Will one Property contain many Buildings? many Units? If so, a table

BP: Yes, it's done so I will remove references to building and properties..
in the Units tbl.

Good move.

JV:>You run a real risk of hitting the 2000-character limit of data actually
USED in a

BP: I did not know about the 2000-character limit of data actually USED in a
table,
to be sure... you are saying if 50 fields with 40 characters in one form
would crash the application?

Sorry, misspoke - 2000 characters (4000 Unicode bytes) in any one
RECORD, exclusive of Memo fields. So yes, if you have 50 fields with
40 characters each, you'll be fine unless you actually fill all fifty
fields with text. It won't crash, but it will generate an error
message and the record will not be added to the table.

JV:> >ParkingID Long Integer 4

BP: Client has stated 2 max and has authorized said structure but I'd feel
safer to build for possible three.
Can you expand on how I am to resolve this...

You have a Many (units) to Many (parking sites) relationship. The
proper technique would be to have a Parking table (which I'm guessing
you already have), and a UnitParking "resolver" table with fields for
UnitID and ParkingID. If a unit has two parking spots, there would be
two records in this table. This will make searching "Who's authorized
to park in Lot K, Space 23" much easier since there's only one field
to search.

JV: All of these are Events, related many-to-many to Units. Rather than

BP: This is The Missing Table !!! This is the proper structure!

JV:> These last four are possibly derived data - if you have a table of

BP: but client said he only wanted to see a checkbox " I don't care when or
how it was done I just want to a checkmark" ( for stuff like Inspections
etc.)was his response. I guess you'll say it doesn't matter what client said
but I should build the srtucture correctly and then just program my forms to
show client what he wants to see. ?

JV: Even more so than above - these should be RECORDS in an events table,

BP: So I'll put a date field in the events table and will solve that

JV:> Are any of these derivable by using calculations upon the other
BP: We plan to derive the financial data from an linked excel spreadsheet. I
posted a question about this today.
Since yes there are many calculations in the spreadsheet we prefer to leavbe
there and just show results from the speadsheet into the linked fields in
access. Will this work?

Linked and displayed would be ideal, if you don't need to update them.

JV:

BP: Security forced me to rename these so I agree about the image data but
no worries it actually is other type of ole documents and yes we will point
to external linked objects and not embed them


Again John Vinson a huge thanks, you provide an inestimable service to your
fellow ms addicts.
I am very very apreciative.

Glad to be of assistance.

John W. Vinson[MVP]

 

[Guest]

When the internet allows for the transfer of free knowledge and experience in
all the world, we will all be living in a better place.

Thanks John for the summary and clear explanation for the parkings resolver.
Obviously that type of situation will arise often in table design and you
helped me understand the big picture.

Happy Holidays to John, all the Microsoft MVPs, and everyone for that matter


Bernard Piette

 

[Guest]

Good Point Duane,

Back to the drawing board..

Bernard Piette

I would not base my table structure on how a customer wanted to view the
data on a form or report. There is very little justification for creating a
table structure that doesn't allow for reasonable flexibility in the future.

--
Duane Hookom
MS Access MVP
--

Duane Thanks for your input.

to be sure right now there is only one date for each action.
So I agree actually that a copytoconsultantdate and others could be
required.
Point being that if we know all the tasks we can plan the structure in
advance.

My question is the way you propose which seems better to me I'm a little
confused?

What we will see in the forms?

I guess what I'm saying is right now I can see all dates based on the FK
PK
UnitID from tblUnits in datasheet view they are all on one line, wouldn't
I
now have multiple rows for one Unit a record for each DateType ?

Note: I very much apreciate your input, I'm not arguing here, justy trying
to understand

I find this structure fairly un-normalized. Let's just look at this
table:
tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

I would not store "date titles" in field names. For instance, what would
you
do if you needed to add a date for "fire inspection"? I would create a
table
of date types with records containing unique values like Acquired,
Reserved,
Promise To Purchase, ...
tblDateTypes
===============
DateTypeID
DateType "Acquired",...
(you could even add a record for CopytoConsultant so you would know the
date. This would allow you to remove that field from tblUnits)


Then create a table of Unit Dates.

tblUnitDates
=================
UnitID
DateTypeID
CompletionDate
Comments

That's just a start but I trust you understand the normalizing
objective...

--
Duane Hookom
MS Access MVP


TblUnits
Name Type Size

UnitID Long Integer 4 PK
PropertyName Text 50
BuildingName Text 50
UnitName Text 50
ManagementCompany Text 50
UnitCategory Text 50
UnitSize Text 50
UnitFloorNumber Text 10
UnitLocationInBuilding Text 25
ParkingID Long Integer 4
ParkingID2 Long Integer 4
NotaryID Long Integer 4
EvaluationReport Yes/No 1
FullInspectionReport Yes/No 1
PyriteTest Yes/No 1
Insured Yes/No 1
FaxPTPtoBrokerOrCaisse Yes/No 1
CopytoConsultant Yes/No 1
FaxtoNotaryOrMailwChq Yes/No 1
FaxLeasetoBankOrBroker Yes/No 1
NotarizedDate Date/Time 8
CommissionPaid Currency 8
IsLeased Yes/No 1
IsSold Yes/No 1
IsVacant Yes/No 1
IsReady Yes/No 1

tblUnitContactInfo
Name Type Size

PK FK UnitID Text 4
UnitMunicipalLotNumber Text 50
UnitManagerID Long Integer 4
UnitNotaryID Long Integer 4
UnitPhoneNumber Text 14
UnitFaxNumber Text 14
UnitEmailAddress Text 50
UnitWebsiteAddress Text 50
UnitShortDescription Text 255

tblUnitDates
Name Type Size

UnitID Text 4
UnitAcquireDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate Date/Time 8
UnitNotarizedDate Date/Time 8
UnitRenovatedDate Date/Time 8
UnitEvaluationDate Date/Time 8
UnitFullInspectionDate Date/Time 8
UnitPyriteTestDate Date/Time 8
UnitCurrentRentalStartDate Date/Time 8
UnitCurrentRentalFinishDate Date/Time 8

tblUnitFinancials
Name Type Size

PK FK UnitID Text 4
UnitMonthlyRentRevenue Currency 8
UnitMonthlyParkingRevenue Currency 8
UnitTotalMonthlyIncome Currency 8
Augmentation Currency 8
UnitMonthlyRentGuaranteed Currency 8
UnitMarketValue Currency 8
UnitParkingPurchasePrice Currency 8
UnitTotalSalePrice Currency 8
DiscountOnUnitTotalPrice Currency 8
UnitCashFlow Currency 8
UnitROIPercent Double 8
UnitRedexActualSoldPrice Currency 8

Other tables include UnitProjects, UnitTasks, UnitAssignments,
UnitMultimedia etc.

Some field sizes need to be adjusted. This is a small portion of the
database ofcourse.
Regular tables also include employees locations properties buyers etc.
but
this should help clarify what I meant. These are in five tables should
I
leave it or put all in same table so that I can use the tab control in
my
forms easily and move the fields around at will .

I would be glad to include full documentation and or Visio Database
Diagrams
if anyone is intertested in helping with the architectrure. Database
includes
hundreds of fields. :-]

Bernard