Table Structure and Related Form Design

[Bernard Piette]

Real Estate Database: for condo developer.

Is there a recommended number of fields in an access table?

I have a table with 75 fields and could break it up into 5 tables of 15
fields.

Let's say a CONDO for EXAMPLE :

They have all their contact info, financial info, marketing hype etc

BC these ALL FIELDS are unique to the individual condo.PK Would it follow
3nf to leave them in the same table ?Then if you wanted to improve db
performance you would split the table with the primary key being a foregin
key lookup etc. Currently 200 records and will grow to 5000 maybe one day.

Well, that's only the half of it...

Actually here'e the real deal.

My tables are normalized and in fact have the tables all split up but I have
huge problem,

I'm trying to use a tabbed form for my units ... Goes like this

tab1=Units
tab2=UnitDetails
tab3=UnitFinancials
tab4=UnitLeaseAgreement

etc... there are 8 tabs in total.

In form design mode I select tblUnits to drag and drops fields from the
field picker...The other tables have a foreign key to the unitID set as the
PK. First Tab works fine I can see all my data in form view.

PROBLEM: when I go to the second tab and drag and drop the fields from my
second table all the records in the first tab become unbound :-\

So to solve this I would have put everything back into 1 table and then I
could drag and drop whatever fields I wanted unto however many tabbed contols
to my heart's content without any programming etc.

I'm certain many of you have faced this before, yet it seems crazy that I
would want to UNnormalize a databse just so I could quickly create apropriate
forms .

I think I'm going nuts, and bald

 

[TedMi]

If each and every instance of an entity, or at least the great majority, has
the same number of attributes, then all of those attributes should be in one
table. A wide table is more efficient than a join of two or more tables.
However, it often happens that most some instances of an entity have a
smaller number of attributes, and only some minority have some additional
ones. In that case, the "sometime" attributes should be kept in a separate
table with 1:1 relationship to the main table.
How you distribute the fields over multiple or tabbed forms is another
matter. Granted, too many fields onscreen at one time are difficult for the
user to comprehend, so you may want to present them in several screens, say
as separate forms or tabs. Just goes to show that data modeling and UI design
are two different disciplines, each with its own considerations, and each
more art than science.

 

[Bernard Piette]

Thanks TedMi
" A wide table is more efficient than a join of two or more tables. "

Is one of the answers I was looking for.

Bernard

 

[Jeff Boyce]

Bernard

Take some time to scan this (tablesdbdesign) newsgroup. You'll find a
strong consensus AGAINST "wide" tables.

To gain the full range of benefits of using Access, you'll need to spend
some time normalizing your data. Again, from review this 'group, you will
find it quite uncommon to have a well-normalized data structure that holds
as many as 30 fields in one table.

You may need to step away from the keyboard and spend some time designing
your data structure. Please note that how the data gets stored need not
impact how you decide to display the data to users. Tables store data,
forms & reports display it (and queries retrieve it).

Regards

Jeff Boyce
<Office/Access MVP>

 

[Bernard Piette]

Thanks Jeff, for the heads up.
In my other post I descibed some of the fields

I have this strucured in five tables but when I try to use tabs contol on my
forms it will only show me data from one table chosen at the time of form
design, So if my tab for units is working then when I click on the
unitfinancials Tab which is based on the unitfinancials table all fields
become unbound? and shows Name? everywhere. But if I put this all in one
table then I can play around with the tabs control all I want because data
comes from same table. Am I missing something here?

" Forms Construction Concept I mean "

I've added these to give a sampling, some renamed on purpose due to
confidentiality and don't worry about the sizes just to give an idea...


Name Type Size
UnitID Long Integer 4
PropertyName Text 50
BuildingName Text 50
UnitName Text 50
ManagementCompany Text 50
UnitCategory Text 50
UnitSize Text 50
UnitFloorNumber Text 10
UnitLocationInBuilding Text 25
UnitParkingID Long Integer 4
UnitParkingID2 Long Integer 4
UnitEvaluationReport Yes/No 1
UnitFullInspectionReport Yes/No 1
UnitPyriteTest Yes/No 1
UnitInsured Yes/No 1
FaxPTPtoBrokerOrCaisse Yes/No 1
CopytoConsultant Yes/No 1
FaxtoNotaryOrMailwChq Yes/No 1
FaxLeasetoBankOrBroker Yes/No 1
UnitNotarizedDate Date/Time 8
UnitCommissionPaid Currency 8
UnitIsRented Yes/No 1
UnitIsSold Yes/No 1
UnitIsVacant Yes/No 1
UnitMonthlyRentRevenue Currency 8
UnitMonthlyParkingRevenue Currency 8
UnitTotalMonthlyIncome Currency 8
UnitRentAugmentation Currency 8
UnitMonthlyRentGuaranteed Currency 8
UnitMarketValue Currency 8
UnitParkingPurchasePrice Currency 8
UnitTotalSalePrice Currency 8
UnitDiscountOnTotalPrice Currency 8
UnitCashFlow Currency 8
UnitROIPercent Double 8
UnitRedexActualSoldPrice Currency 8
UnitAcquiredDate Date/Time 8
UnitReservedDate Date/Time 8
UnitPromisetoPurchaseDate Date/Time 8
UnitMortgageApplicationDate Date/Time 8
UnitMortgageAcceptanceDate 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
UnitMunicipalLotNumber Text 50
UnitManagerID Long Integer 4
UnitNotaryID Long Integer 4
Name Type Size
UnitPhoneNumber Text 14
UnitFaxNumber Text 14
UnitEmailAddress Text 50
UnitWebsiteAddress Text 50
UnitShortDescription Text 255
UnitLeaseID Text 25
UnitLeaseNotes Memo -
UnitTenant Long Integer 4
UnitDocumentNumber Text 30
UnitLeaseStartDate Date/Time 8
UnitLeaseFinishDate Date/Time 8
UnitMoveInDate Date/Time 8
UnitMoveOutDate Date/Time 8
UnitRentalFee Currency 8
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 -

 

[[MSFT]]

With mutiple tables, I think you can create a query from these tables and
use the query on your form. Can this help on this issue?

Luke

 

[Pat Hartman\(MVP\)]

Creating four 1-1 relationships didn't normalize your database. There is
nothing wrong with having all the fields in a single table provided they
actually occur only once per condo. However, without seeing your field list
I can only guess that you have repeating groups and THOSE need to be
normalized. One repeating group that you probably have is tenant. Since it
is possible to have more than one tenant sign a lease for a unit, tenant
info belongs in a separate table. If you are responsible for utilities or
other expenses, those items belong in a separate table.

A bound form has one and only one RecordSource. Tab pages do not have their
own RecordSources. If you want each tab page to display data from a
different table, create subforms that are bound to the correct RecordSource
and place a different one on each tab page.

 

[Bernard Piette]

YES YES YES "Pat Hartman(MVP)" wrote:
A bound form has one and only one RecordSource. Tab pages do not have their

own RecordSources. If you want each tab page to display data from a
different table, create subforms that are bound to the correct RecordSource
and place a different one on each tab page.

Thanks so much Pat, that is exactly what I was looking for....

Bernard Piette

 

[Tim Ferguson]

" A wide table is more efficient than a join of two or more tables. "

Is one of the answers I was looking for.

Unfortunately it's the wrong answer.

Database design is not about efficiency: thirty years ago disk space was
expensive, processors were slow, and designers had to cut every corner
they could in order to avoid long delays for users. None of these is now
true, and the new "efficiency" is "integrity".

If you are happy with having three different billing addresses for the
same customer, then you don't need to know about R theory. If it doesn't
matter that you can't list the vehicles that have _not_ been hired during
this financial year, then you don't need to know about R theory. If you
cannot count how many patients are attached to each doctor (because they
have been spelled like J. Roscoe, J Roscoe, Joe Rosco, Jo Roscoe etc),
then you don't need to know about R theory.

The rest of us do care about having a database design that is rigorously
correct and provably so. It's about the only area in computing that you
can give such guarantees, and it's only since the sixties or so that the
mathematics has been worked out and applied in this area. To be sure, you
can put two fingers up to the likes of Codd and Date and Celko but don't
expect much sympathy from your users/ commissioners afterwards.

B Wishes


Tim F

 

[Bernard Piette]

Tim Ferguson,

Thanks for your input, I agree wholeheatedly.
I wanted a simpler architecture so that I could just cut and past some to
build forms but data structure abd presentation are different species and
both have to be handled with their own sets of principles. And thus I will be
splitting my tables up as much as possible to follow 3nf

Thanks Tim,

 

[Pat Hartman\(MVP\)]

Should we take this to mean that you are ignoring the advice regarding
normalizing the structure and/or combining the data to eliminate the 1-1
relationships?