Database Foundation

[MarkK]

I have made a table like this before, that the thing might be the child of many parent types. In this case we can link a cost to a tenancy OR a unit OR nothing.

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

You should ensure only 1 FK has a value, and you can't enforce RI.

 

[Valery]

Ok - sounds right... (what do I know, lol) - just wondering why the link to TenancyID vs TenantID? Or maybe I should think about it before asking you...

 

[Valery]

Oh! JDraw, just read your message. THANK YOU. Really exhausted right now so will definetely look at this in detail when I am feeling better. TYTYTYTY

 

[MarkK]

why the link to TenancyID vs TenantID?

Because Tenancy gives you the Unit AND the Tenant. Link to just the Tenant and you have no definite link to the Unit, since a Tenant might move units.

 

[Valery]

Got it~ thank you for your patience everyone!

 

[Valery]

Hi,

Here is where I'm at with relationships (see JPEG). Not all tables are in yet. I am learning and taking my time to insert them as I understand the concept. Major awakening for me and super fun!

Please take a look at the links and let me know if I am in the right direction. What I am unsure of is the link to the tblTenantSubsidy. Unsure because not EVERY tenant gets a subsidy.

Every tenant's rent is based on Market Value minus Subsidy both being:

Market Value (one amount for each unit type (1-bed, 2-bed...)
Subsidy (Monthly allowance paid on behalf of the tenant's of a given Unit)

My issues here are:

1) I don't know if I should have $0.00 where the tenant receives no subsidy or if the record should just not be there...

2) The subsidy ONE amount but given and signed by all tenants. In fact, if ONE tenant moves out (transfer, death, divorce...), the subsidy is re-evaluated - may stay as is, may not.

So linking it to the tenant table enters the amount as many times as there are tenants which seems foolish. But linking it to the unit seems weird as the subsidy is given to people and the allowance and it's amount has nothing to do with the unit or unit type - it is based on the tenant's revenues...

So I am somewhat confused.

As for the suggested Cost table... I will need to deal with this way down the road as, for now, most financial aspects are done in Excel. Unsure how much of it will be taken over by Access or "simply" linked...

Thank you! Don't quit on me yet, lol !

FYI: MRO table stands for Maintenance, Repair and Overhaul (documented in the Table Properties and current on-going Database Procedures).