Database Foundation

[Valery]

Hi all - not sure how to phrase my question and concern.

This is a COOP habitation and there x number of units. One or more tenants may live in a given unit. Tenants status are "members" (M) or "occupants" (O). All members are of equal rights and obligations...

Here are my two main tables - the heart of the database:

Table: tblUnits
UnitsID – primary key
UnitNum – number field
UnitType – number field (looks up a query)

Table: tblTenants
TenantsID – primary key
Unit_Num – number – link to tbl Units
FirstName, LastName, DOB… and so on

I link the tables by UnitNum and Unit_Num.

Problem: In a query, because there are several tenants to a given unit - therefore they have the same UnitNum, the recordset not updatable.

So what should I do or have done?

My logic was to have the Unit table contain information that pertains directly to the Unit itself i.e. status of the unit (clean, dirty...), number of appliances and types of appliance, flooring type and condition... and, already in there, the UnitType which contains information as to the number of bedrooms the unit contains and so on.

Before asking for my help, my brother-in-law had built a small database and had the tenants in separate fields vs records. So he had fields like member 1, member2, member3… Occupant1, Occupant2… with tenant's full name in the field. Of course, they rang in snags at some point where a unit had more people than anticipated by the database.

I get that doing this way is easier to produce labels for example, or have all the tenants in the salutation of a letter... but it is wrong. So it is not my solution.

Any suggestions how I should build this? And for my non-updatable query which also means non-updatable form?

Thank you!

Val

 

[plog]

Re: Database Foundation - URGENT help needed

You should build forms to interact with data. You base those forms on tables, not queries.

This means you would have a form based on tblUnits and form based on tblTenants. Because of the 1-many realtionship, the Tenants form could be a subform on the Units form.

 

[Valery]

Re: Database Foundation - URGENT help needed

I get it - but here, for example, I need an updatable CONTINUOUS form that contains some tenant information WITH the unit's number of bedrooms which is in the Unit table.

And aren't forms also based on queries? I mean, that is certainly not objectionable - not from the courses I took (as few as they may be).

 

[plog]

Re: Database Foundation - URGENT help needed

I get it - but I need fields on ONE form that are in both tables

What does that mean? If you use a form/subform you get all the fields you need. Forms that directly interact with data (add/edit/delete) should be based on tables, not queries.

Lastly, I don't understand how you have 2 subforms, when we are only talking about 2 tables. What's the 3rd datasource?

 

[jdraw]

Re: Database Foundation - URGENT help needed

According to your description

1 Unit may contain 1 or many Tenant(s)

UnitId is PK of tblUnit

UnitId (long integer) should be FK to tblUnit within TblTenant
not UnitNum. You join tables on keys.

When you start Member1, member2, member3 you know you have a normalization issue.

As for Form you'd have a Form/Subform.
Form for Units and subform for Tenant.

 

[Valery]

Re: Database Foundation - URGENT help needed

Thank you jdraw for a clear response.

for 1 Unit may contain 1 or many Tenant(s) - Yes

UnitId is PK of tblUnit - Yes

UnitId (long integer) should be FK to tblUnit within TblTenant
not UnitNum. You join tables on keys. - this is one of my question answered. Therefore, I would have the field UnitNum which is - let's be clear - the apartment number, only in the Unit table?

When you start Member1, member2, member3 you know you have a normalization issue. - Agreed. That is why I did not build it the way my brother-in-law did. But you must admit it is very difficult to produce labels for example. They need labels that have ALL of the tenants (members) on it - one below another.

As for Form you'd have a Form/Subform.
Form for Units and subform for Tenant. I DO - that is EXACTLY what I have - but on a continuous form - which I want - I will not be able to have the UnitType - not without a query and not updatable.

But I get it. I will make the change (PK - FK). Thank you. I am keeping the thread unsolved - as I may have questions after I have changed this. It is becoming clearer - thank you for setting me on the right path.

 

[MarkK]

I also think this is a many-to-many problem, for instance, there is a Tenant, and a Unit, but there is also an "object" that defines the relationship between a tenant and a unit in time. For instance, what if a tenant moves units? One agreement ends, and a new a agreement starts, but if you don't have a table for this tenancy agreement, then all the data for the old tenancy is destroyed. In that case, there is a serious problem with that model. This model is much more likely to be what you are trying to achieve...

tTenant
TenantID (PK)
Name
Phone
...

tUnit
UnitID (PK)
UnitNumber
...

tTenancy
TenancyID (PK)
TenantID (FK)
UnitID (FK)
StartDate
EndDate
Rent

It is this last table, Tenancy, that is your crucial table, since it defines the Relationship between a Tenant and a Unit, which is what your system is really getting at. And this solves your case too where maybe you have many tenants for a single unit.
Hope this helps,

 

[Valery]

Re: Database Foundation - URGENT help needed

WOW - love it! I did have a system in place for keeping all the info for the Unit - like who lived there last year, last month... I had it working with MoveInDate and MoveOutDate. Especially that many of them move to another Unit with more bedrooms vs moving out completely of the COOP.

But what you are proposing is so much "cleaner" - IT IS the answer I was looking for - I instinctively know it. I have never worked like that so may need help with forms and so on.

I will implement these tables and adjust the existing data this evening. A BIG THANK YOU to all of you helping out.

Mark - you rock!

Oups! please answer this one question: Just so I am clear on it - there will be no "direct link" between the tenant table and the unit table. They will "talk to each other" through that 3rd table, right?

 

[MarkK]

Just so I am clear on it - there will be no "direct link" between the tenant table and the unit table. They will "talk to each other" through that 3rd table, right?

Yes, exactly. A tenant and a unit are completely unrelated, except via the tenancy. This is a classic many-to-many relationship.

 

[Valery]

Mark (or other wizzzards!) - I have create tblTenancy and all the data is where is belongs. I am setting the relationships. Can you advise on the type and if I select referential integrity? I am asking for the three main tables: tblTenant - tblUnit - tblTenancy.

Thank you!!

 

[jdraw]

What do you have currently in your relationships window? Post a jpg.

This article may help.

 

[MarkK]

I would enforce referential integrity.

 

[Valery]

Mark, I am confused again! Where should these fields be located, in Tenant, Unit or Tenancy?

InspectionDate (may be several Inspections within a year...)
InspectionBy (name)
InspectionReason (choices limited to Annual, FollowUp...)
Unit Condition (select 1 to 5)

Just unsure as for example the Unit Condition relates directly to the Unit (duh!) but also to the current tenant(s).

It cannot be in Tenancy as there are multiple records for one unit (it includes as suggested the tenantID, so multiple records to one unit).

If I create another table, what will the link be to show which tenant(s) were in Unit X when this occured? I guess date... makes it harder to query for newbies...
Thanks!

 

[MarkK]

INNER JOIN. Not LEFT JOIN or RIGHT JOIN, so don't right click on the relationship and select "All records from one table, and those that are equal in the other," or however they phrase it. Drag and drop a relationship between your PK and FK, and then enforce referential integrity, yes to cascade update and cascade delete. IMO.

Your concept of a valid Tenancy REQUIRES a Unit AND a Tenant. It is meaningless if one of those is missing, so if you enforce referential integrity a unit and a tenant can exist without a tenancy, which is correct, and a tenancy cannot exist without a unit AND a tenant. The Tenancy table will reject a record that doesn't have both links, which makes sense.

 

[Minty]

You could have a tenant call Empty or UnOccupied ?
That would allow you to report on all costs incurred while a unit was vacant

 

[Valery]

Yes. My thought was to insert "Vacant" in the Surname field with a StartDate, EndDate. Glad you agree. But it would not be ONE tenant. This would be inserted at anytime, in any and every unit, that is vacant. So there could be several tenants named "vacant" at one time, for a given period.

Still makes sense? Mark - what are your thoughts?

 

[MarkK]

Costs, it seems to me, are related to the Unit without respect to Tenancy, and Vacancy should not need to be defined as a tenant. Vacancy is the absence of Tenancy. But to the costs, I would do...

tUnit
UnitID (PK)
UnitNumber

tCost
CostID (PK)
UnitID (FK)
CostDate
Description
Amount

. . . and in this case, if I wanted to record costs against the building as a whole without respect to particular unit, then leave the UnitID of the cost null. This is a case where you would not enforce Referential Integrity on UnitID, since a "whole building" cost does not have a "parent" unit, whereas RI would require you to define one.

 

[The_Doc_Man]

Minty, the correct approach would be to test that there is no currently valid Tenancy record for a unit and therefore it is unoccupied. Though it is very easy to include a flag, that technically is a denormalization because that flag in the unit table is a field that depends something that does not ONLY depend on the unit table's PK. (It also depends on the dates in the Tenancy table). Having said that, it isn't something I haven't done in the past, and it's not like it would be that hard to maintain. Valery, Minty's suggestion WOULD work - but if you were worried about normalization, then read my response carefully to see why it is an issue.

Regarding knowing who was in that unit during performance of maintenance, the maintenance records don't depend on Tenants, they depend ONLY on a unit, because you can do maintenance on an empty unit. Therefore, entity analysis suggests that maintenance records would have to be a separate table from the other three tables - and related to the Units table as a "Many Maintenance Events per One Unit."

If there is a reason that you want to know who is in a unit at the time of maintenance, this is a query that requires you to know one of the "Old Programmer Rules." It is that Access won't tell you anything you didn't tell it first. Which is why the tenancy table needs to include a start and end date. Then, to find out who was in the unit on date X, you need a query that includes the date of maintenance something like this (and I'm synthesizing names just for illustration). Let's say that your unit is occupied when at least one Tenancy record does not have an EndDate value.

SELECT Tenant.FullName FROM 
( Tenant INNER JOIN Tenancy ON Tenant.TenantID = Tenancy.TenantID ) 
WHERE ( #date-of-interest# BETWEEN Tenancy.StartDate AND Tenancy.EndDate )  OR
( ( #date-of-interest# > Tenancy.StartDate ) AND ( Tenancy.EndDate = 0 ) );

Something similar to this, anyway. Of course, the date of interest comes from the date in the maintenance table. This query would, in two tests separated by the OR, find all tenants who were in the unit at the critical date OR who became tenants before that date and still ARE tenants.

You would of course have to figure out how to get the date of interest into the query, but that could be something so simple as that you build this query string via string concatenation methods and then open a recordset to the string as a DynaSet. There are also ways that, if you did this from a form bound to the maintenance table, you could pick up the date from the current record via a control on the open form. Since you said you were relatively a novice, let's just say that you can look up these terms online because I've tried to use correct names for the items you would need to reference.

 

[Valery]

partial answer (rest to follow)

Minty, the correct approach would be to test that there is no currently valid Tenancy record for a unit and therefore it is unoccupied. Though it is very easy to include a flag, that technically is a denormalization because that flag in the unit table is a field that depends something that does not ONLY depend on the unit table's PK. (It also depends on the dates in the Tenancy table). Having said that, it isn't something I haven't done in the past, and it's not like it would be that hard to maintain. Valery, Minty's suggestion WOULD work - but if you were worried about normalization I AM , then read my response carefully to see why it is an issue.

Regarding knowing who was in that unit during performance of maintenance, the maintenance records don't depend on Tenants, they depend ONLY on a unit, because you can do maintenance on an empty unit. NO - maintenance or repairs often done after they leave and leave the unit in a terrible state for example - Need to know repairs were done while vacant - and - to know who was there before that (they may be financially responsible for some of the damage).

Therefore, entity analysis suggests that maintenance records would have to be a separate table It will be from the other three tables - and related to the Units table as a "Many Maintenance Events per One Unit."

 

[jdraw]

Valery et al,

I created a sample generic Tenancy data model after one of the earlier posts (attached).
Hope it's helpful.