Property Management

[shorty]

I am trying to add a layout called property listing to this database and either my relationships are wrong or I have got off to a wrong start with designing this database. I try to get a property listing that has all the properties and the name of tenant occupying along with phone numbers. I get either the property list and no tenant names or I get the same tenant living in all the properties. I have enclosed a copy of the database if someone could critic me and see if I have gone off base. Thanks

Property_Tenant_System_Clone.zip

[Fenton]

First, you seem to have some ambivalence about IDs. You've got auto-enter serial IDs, but then you're using combo field data as keys also. Here's the order I think of ranking ID types for databases.

1. Auto-enter serial ID

2. Fixed ID (such as a "parcel number", which is government issued and will never change

3. Combo data fields

In the case of properities I think you might get away with #2, the parcel#. Otherwise use a real auto-enter serial ID. Don't mess about with combo data fields. For one thing, it is possible that a typo in a number or name will not be discovered until after you've linked things up, which makes it a pain to fix.

Same with Tenants. Always use an auto-entered serial ID for people. Names are unreliable.

I think you probably need a Units table. But, this is a little tricky. Because you don't want to have to run a single-unit property thru Units also. I think you can just have Units for properties that need them, and hang an instance of Tenants off each one. It's one of those 6 of one, half-dozen of the other things.

[P.S. Please clean up your Relationship Graph. I don't see how anyone can think when their graph is all expanded and lines are crossed all over the place -]

Edited July 27, 2007 by Guest