Which table should have this relationship?

[MickFlanagen]

Hello all,



Thanks in advance for your help.

I am trying to create a database for leases and lease proposals. Here are the tables I am concerned about:
Lease (tenant name, rent, proposal/signed)
Space (Floor, building, office/retail)
Measurement (as of date, measurement 1)

There is a one (lease) to many (space) relationship because a lease may cover many spaces, and a one(space) many (measurement) relationship.

The reason I have a "measurement" table is because suites change sizes, so this allows the user to say 'as of Dec 31, this suite will now be 5,000SF'. Hence the one to many relationship.

Now, the problem is that when I go to create a proposal I want them to be able to include a new measurement. However, if the proposal doesn't go through or there are multiple proposals, I don't want the measurement to apply.

So, why not just make the measurement related to the lease? Because if the space is vacant, there will be no lease, and so it will 'disappear'.

Any suggestions on how to handle this would be greatly appreciated.

Thanks,

Mick

[rzw0wr]

Can you upload your database?

[MickFlanagen]

Absolutely, here it is.
WorkingDatabase.zip
Thanks,

Mick

[rzw0wr]

Mick,
I would suggest you look into table normalization.
It appears to me you have a few too many tables and you are breaking things down too far.
Some of the tables you have do not appear to be normalized.
Try starting with a few basic table that you need, get them normalized and add more as you need them.
I can see this database tuning into a monster if you don't.

If I can be of help please let me know.

Dale

[MickFlanagen]

Thanks Dale, I have heard about that and done a bit of reading, but I will definitely do some more. Thanks for the input.

Mick

[rzw0wr]

Mick,
I think it really help you. You are doing way too much work for what you want to do.

Let us know if we can help along the way.

Dale

[MickFlanagen]

Dale,

(warning, wall of text based on weeks of frustration from an excel guy trying to create an access database)

Based on my reading, it seems like this is the goal: every field in a table should depend completely on the primary key and nothing else. If it depends on something else, that should be linked via a foreign key/relationship.

I will take another look at all of my tables as soon as I get back to the office tomorrow, but this is what I had been aiming for, which is why I have broken things down the way I have.

As you may be able to tell, I'm thinking about it like an org-chart. So for example, suite 200 is on the second floor, but there are lots of suites on the second floor so to avoid duplication I should have a separate table. the "second floor" is really just one floor in phase one of building A, so to avoid duplication.....you see where I'm going here

Or, you have a negotiation, which involves many proposals, and each lease proposal has its own different distinguishing factors like rent, different suites and start date etc etc

Do you think you could give me an example of a relationship or table that highlights my mistake?

Using the example of the problem I was trying to address in my original post, the question I am trying to answer there is "how big is the space on date x". I figured this depended on the space, but also a measurement, so I created a new table for measurement with space as a foreign key.

The one exception that jumps right out at me is the 'rent' table. I really didn't want to create that table, because I knew I had all of the building blocks I needed to calculate each payment (start date, term length, rent increases, months of free rent etc etc), but the thought of writing a code that calculated the rent for 10,000 tenants every time I needed to do this seemed wrong. If I wanted to get the sum (or more realistically the net present value) of all of our rent payments, that would require me to calculate each payment from my basic data, which would take approximately forever, so I just caved and made a table to store all of these rent payments.

To provide some background, here are the types of questions I want to be able to answer with my database:

How many SF do our top 10 tenants take up?*

what is the vacancy in a (building, city, region, country), which can be further subdivided by the type of space (office/industrial) How does that compare to the market (eg industrial space in Suburban Chichago)?
(I didn't even make a table to input the vacancy for each market and space type yet!)

How many new leases did we sign this month? How many SF did that represent? What is the sum of the value of their rent payments? How does that compare to our budget forecast? How does it compare to last year? (with the same subdivisions as above)

What is the sum of the remaining rent payments (subdivided as above). What is the Present Value of those payments (based on the discount factor unique to each building)

what is the average rent per square foot (again, as above)

how many square feet will be vacant on Jan 1 2014 in (again, as above) if we do nothing? What if we meet our budget assumptions?

How does this lease proposal compare to our budget assumptions? What about our appraisal assumptions?
How have our leases compare to these benchmarks on average? (subdivided, as always)

How does this lease proposal compare to our original offer?

How many lease proposals are at a certain "level" (eg. accepted but not signed, first offer, counter offer etc)
(again, subdivided)

*i think this question is a good example why I have organized things the way I have, so may be the best way to show me why I'm wrong.
context:
In the past, we have had many "sub-tenants". For example, the ministry of transportation and the ministry of health, (or worse yet "Walmart Toronto" and "Walmart USA" etc.) I want to make sure that their leases and proposals are separated (so I can say, "how many square feet does the ministry of transportation take up") but also that we know that they are really at the end of the day, just part of the government of Ontario. From a risk management point of view, that is significantly more important, because it is the ability of their parent company to pay rent that determines the riskiness of our lease portfolio.

One of the reasons I separated subtenants and master tenants was specifically to force the user to be 100% sure we didn't already have an entry for that master tenant. Maybe this was going a step too far.

[June7]

My 2 cents - It is a balancing act between normalization and ease of data entry/output. At some point might say 'enough, I will tolerate a few empty fields on some records because it suits my needs. Also, memory is cheap now, sometimes just more convenient to save actual text instead of linking key.

[MickFlanagen]

Thanks June7, I am still trying to do some more reading on normalization, so your feedback is definitely helpful. It sounds like I may have gone a step too far, so I am going to review each of my tables to see if they really need to be separated.

One example I thought of was my Termination Options.

I currently have it set up so that each lease is a table, with a one to many relationship with termination options. This would the user to input multiple termination options on the same lease. Is this the type of thing I should be eliminating?

Going back to my original issue of the measurements, I had originally included them in the same table as 'space' just as a column "number of square feet", but when I realized that I wanted to be able to store a different measurement for today, yesterday and tomorrow, I created a new table. Would you eliminate this?

Thanks again for all of your help.

Mick

[June7]

If you want to allow a lease agreement to have any number of termination options, then a child table does seem appropriate.

Not understanding the measurement issue. Why would the SF for a lease change? Wouldn't that be a new agreement?

[MickFlanagen]

Sometimes the space changes just because the original measurement was wrong (more common than you'd think, especially for buildings we purchase), but most of the time it would be a new agreement.

However, if I make the size of the space dependent on the lease agreement, if a space is vacant than it will 'disappear'. More importantly, it requires the user to enter the SF of the space every time they create a lease or proposal, which leads to a ton of errors. That is how our current system works, and it is one of the main reasons I am doing this project.

Our current system also relies on the user making an offsetting vacancy record when a lease expires, but again, that tends to lead to a lot more user error (or in many cases, the record is just never created).

[June7]

Not sure what you mean by 'disappear'. Doesn't the Space table have records for each defined rentable area?

[MickFlanagen]

Not sure what you mean by 'disappear'. Doesn't the Space table have records for each defined rentable area?

I have two options: make the square footage a function of the lease, or of the space. It would only 'dissapear'if it was a function of the lease. However, if I make it a function of the space, then what happens when a new tenant rents the same space with a different square footage?

Here is a more complete example:

The 3rd floor is 20,000SF. Currently tenant A rents half (3a) and tenant B rents the other half (3b). No problem.

Now, on Jan1 tenant A will be moving out and tenant B will be expanding by 5,000SF

So if the square footage is a function of the lease, I have a new 5000SF lease with B, but no agreement for the renaming space, so it 'dissapears' On the other hand, if the square footage is a function of the suite, I can just make one suite bigger, and the other suite smaller. Problem solved.

Now let's imagine that the expansion of tenant B is not a done deal (we're still negotiating) and tenant C comes along. Tenant C wants to rent 7,500 square feet. How do I create my lease proposal for tenant C AND tenant B who both want different amounts of space if my system stores one SF number based on the primary key of the suite?

I can't change the size of the suite every time I make a new proposal (which may or may not go through), so I somehow need to be able to store different measurements, and know which ones "count" and which ones don't.

[June7]

Getting out of my comfort zone so I Googled: Access database building lease property management

Got some hits for templates but most probably have a cost, like: http://www.microsoftaccesstemplate.com/info/rental.asp

And here is ERD http://www.access-diva.com/dm3.html

[Dal Jeanis]

Mick -

One of the ways that I approach review of a database design is by creating Use Cases that mirror the Business Rules, and seeing whether the proposed design SUPPORTS each given UC/BR, or the proposed design BLOCKS each given UC/BR. A Use Case is just a 2-3 sentence paragraph that says, WHEN (this happens), THEN WE (Do something).

WHEN we become aware that the square footage for a space is wrong, AND the lease contract is written in terms of the overall dollars per month, THEN WE update the square footage on the database and make sure to get it right in the next lease.

WHEN we become aware that the square footage for a space is wrong, AND the lease contract is written in terms of the dollars per square foot, THEN WE place a note on the record and refer to legal/management for decision on whether to refund/ignore/waive/collect the discrepancy.

...And so on, through every everyday situation you can think of, and every question you can think you might like to ask. You have a bunch of great questions, so you're definitely doing the right analysis.

After a little thought about your business flow, my gut feel goes like this:

SPACE DESIGN

A Space should represent the physical location, whether or not it is leased at any given time. It should have fields that represent valid-from and valid-to dates for that configuration. The valid-to would be NULL or arbitrary-far-future-date until there is a contract to reconfigure the space. Any time the space is reconfigured, the valid-to date on that space would be set, and a new space record created for the new configuration(s). If one space is broken up into three, the first space record is ended, and three new ones are begun, and vice versa.

An Overlap table should provide a link to identify spaces that overlap. For instance, if spaces A and B merge to become space C, then there should be a record for A=C and a record for A=B. As a validity check, it would be an error if there were a record on the overlap table where the two overlapping spaces have leases that are valid on the same date. (This is how to avoid your multilevel breakdown dilemma - each space record is a space record, and could be a single closet or several floors.)

A Proposed Space might conceivably be on the regular space table with a flag, but my first instinct is to put it on a parallel table of its own. When the contract is signed, the Proposed Space would be moved to the actual Space table, its valid-from date set to the (future) beginning of the lease, and the valid-to date set on the old space. Even a space that isn't changing configuration can be copied to this proposed space table if it is part of a proposed lease. This minimizes special handling. When the lease is accepted, since there are no differences, the cloned proposed space could be discarded, or it could be retained, either strategy produces the correct result.

LEASE

As with your current design, a Lease can be related to many Spaces, both at the same time and at different times. I'd have to know a lot more about your business usage to comment on this - sounds like you have a design that already deals with tenants and subtenants and guarantors and so on. I'd tend to have a master lease and riders, which may or may not be related to any particular space(s). That means that the relationship between leases and spaces, leases and riders, spaces and riders would be on separate tables, if you needed all of those..

Put your Proposed Leases into a parallel table, and treat it the same as the proposed space table. If the lease gets signed, move the lease over, and move over each of the proposed spaces associated with the lease.

RENT TABLE

Don't forget to look at the characteristics of the underlying real life object when you're developing and normalizing your database schema.

Here's how I look at the question about your rent calculations and projections - you don't get rent by calculating the square feet a space should rent for this month, you get it by having a lease contract that makes a specific tenant pay you the rent. Other than at proposal time, the monthly rent you collect is NEVER calculated based on the square feet, it's a function of an agreed contract. So the monthly rental amount that you are being paid for a lease is a characteristic of the lease, not a function of the square feet or anything else. Accounting-wise, square feet can (should) be treated as a comment field, not the source for a calculation related to actual income.

On the other hand, when you are doing queries to project future rent, or compare contracted rates to current or future market rent, you will probably want to create temporary tables that contain the calculated values. Your mileage may vary.