Design problems

[TracyBell]

I need a fresh perspective on a database I'm working on for a property management company and this may be a newbie question.



This database must track the owners, contacts, property, units and tenants - to give you the big picture.
Properties could be owned by one or more companies or individuals.
Properties could have one or more contacts who may or may not be the owner.
*This is where my head starts spinning-probably because I've been looking at it too long*
To date, this information has been kept in multiple Excel spreadsheets and tracked by a management agreement number, which they refer to as the "Owner ID". Each property and unit also have numbers, so the full ID they use looks like this:
PM-001-001-001

The client would like to see a report which shows all the contacts for a particular agreement like this:
PM-001
Person 1 name and contact info
Person 2 name and contact info

We'll also need the same contact report for properties, as well as a tenant report, which I believe will follow the same schema.

In between I have all kinds of forms to allow easy data entry, but I'm beginning to consider scrapping a bunch of it and starting over. Especially since I just discovered that what they've been referring to as the "owner" list is really the "management agreement" list and people or companies are listed on there multiple times. I know I should have seen this sooner, but here I am. I would really appreciate any advice! This is my first client, so I'm basically a newbie, although I've worked with databases for a while.
Relationships view is attached.

[jzwp11]

Welcome to the forum!

Looking at your relationship diagram, your database is not normalized. For more on normalization, you may want to check out this site for an overview. Since the tables are not normalized, I would recommend starting fresh.

Since a company is just a collection of people, you could have companies and people in 1 table (contacts, owners, associations & tenants)

tblPeople
-pkPeopleID primary key, autonumber
-txtPName will hold the surname of a person or the name of a company
-txtSName will hold the first name of a person, null for a company
-txtAddress
-txtCity

If an individual is related to a company, you could relate them as follows:

tblPeopleRelationships
-pkPeopleRelationID primary key, autonumber
-fkPPeopleID foreign key to tblPeople
-fkSPeopleID foreign key to tblPeople


Since a property can be owned by many people/companies or a property can have one or many contacts, or a property can have a related association:

tblPropertyPeople
-pkPropPeopleID primary key, autonumber
-fkPropertyID foreign key relating to your property table
-fkPeopleID foreign key relating to tblPeople
-fkRoleID foreign key to tblRoles (what role the person or company play with respect to the property owner, contact, association etc.)

tblRoles (a table to hold the possible roles)
-pkRoleID primary key, autonumber
-txtRole

Additionally if a person/company has many contact methods (phone, fax, mobile, email), that describes a one-to-many relationship

tblPeopleContact
-pkPeopleContactID primary key, autonumber
-fkPeopleID foreign key to tblPeoples
-fkContactTypeID foreign key to tblContactType
-txtContact (field to hold the actual phone number, email address etc.)

tblContactType (holds the type of contact: business phone, cell phone, e-mail)
-pkContactTypeID primary key, autonumber
-txtConactType

Now as to your existing tables, I see in tblProperty that you have extfaucet1, extfaucet2... Sequentially numbered field names are typically a sign of repeating groups which is a clear sign that your tables are not normalized.

You would store the various items such as faucets in a table and then relate them to the property as individual related records

tblItems ( a table to just hold the items)
-pkItemID primary key, autonumber
-txtItemName

Now relate the items to the property

tblPropertyItems
-pkPropertyItemID primary key, autonumber
-fkPropertyID foreign key to tblProperty
-fkItemID foreign key to tblItems

I see in tblUnits you have specific items that describe thing in the unit, again those items should be records in a table

tblUnits
-pkUnitsID primary key, autonumber
-fkPropertyID foreign key to tblProperty

tblUnitItems
-pkUnitItemID primary key, autonumber
-fkUnitID foreign key to tblUnits
-fkItemID foreign key to tblItems

Since a unit can have many tenants over time that is a one to many relationship. A tenant is a person, so they should be listed in tblPeople

tblUnitTenants
-pkUnitTenantsID primary key, autonumber
-fkUnitID foreign key to tblUnits
-fkPeopleID foreign key to tblPeople

Since you have 2 dates associated with a tenant in a unit (Move In/out), that describes a one-to-many relationship

tblUnitTenantEvents
-pkUnitTenEventID primary key, autonumber
-fkUnitTenantsID foreign key to tblUnitTenants
-dteEvent (date of the event)
-txtEvent (move in or move out or any other event)

[orange]

There are a number of existing data models, some of which may be applicable to your situation, here http://www.databaseanswers.org/data_models/index.htm

An approach to database design is given here
http://www.databaseanswers.org/approach2db_design.htm

There is a video tutorial on Normalization here.
http://www.youtube.com/watch?v=uO80f...eature=related

I would recommend that you write a 2 or 3 line definition /description of each of your entities to assist in understanding what each thing is (and isn't).It will prove more useful than you think and can be used for documentation and communication with others.

Build a data model
Get some test data
Test your data against the model and adjust as needed.

[TracyBell]

I think my biggest problem so far has been I'm trying to think of how they currently do things and design my database to fit their model, rather than focusing on normalization and adjusting their business practices to be more efficient. But that approach is obviously not working.

Thank you for your advice and suggestions. I will take a closer look at it tomorrow and reply again if I need any more clarification.

[ssanfu]

Also see this tutorial:

Access Basics by Crystal

http://www.accessmvp.com/Strive4Peace/Index.htm


As you can see, except for the simplest of flat files, you have to forget about "how it's done now" and create a normalized structure. Then, you can still make it look like what you have now, or make it more efficient to input data and get data out in forms/reports.

As you can see, it takes a little art and a little science to create a workable table structure. I can add nothing to what jzwp11 gave you.

Good luck.

[TracyBell]

jzwp11 - a quick question. Since I have to keep their existing IDs, would you recommend adding a field for those or should I use that as the PK - but not autonumber?

[jzwp11]

I would leave the primary key as an autonumber and create another ID field to hold the ID that has significance to the user. Using the DMax() function you can increment that ID field. That field must be a number in order to be able to increment it. So for something like this: PM-001-001-001, you would have to construct it by concatenating the various fields in conjunction with using the format() function. Are all three 001 portions incrementing?

[TracyBell]

jzwp11, yes they are incrementing. Wow - I've got a lot of learning to do! I'm always amazed by how much I don't know about Access, even after working with it for several years. I really appreciate your help!

[jzwp11]

What do each of the terms in the PM-001-001-001 represent? The actual incrementing fields might be in different tables I'm guessing.

[TracyBell]

PM-001 represents a particular management agreement we have with that person/people/company.
The next set of three digits is the property.
Next set of three digits is the unit.
Then we've added another set of two digits for the tenant.
We've also been discussing another set for each tenancy. Does that make sense?

They are currently using the database as is and I'm working on normalization based on the information in this thread. I plan to import the data from the other database into the normalized database. One of the things I'm doing differently is changing some of the autonumbers to just numbers, but primary and foreign keys will remain as you recommended. Do you think that will still work?

[jzwp11]

Keeping the primary and foreign keys such that they do not get involved in your PM-001-001... code number sequence is probably preferred.

Any time a new record is added in any of the tables that make up the code number you will have to have a routine to increment the number field. To display the code number, you will have to first format each of the number fields from the respective tables and then concatenate them. This can be done whenever you need to display the code number

[TracyBell]

It's been a while since I worked with foreign keys. Is it simply considered a foreign key because it is a primary key brought in from another table - or does it have to be a part of the primary key for the current table as well? For example, do I have to select all of the "key" fields to create a combined primary key?

[jzwp11]

A foreign key is as you describe:

Is it simply considered a foreign key because it is a primary key brought in from another table

The primary key of one table joins with its counterpart foreign key in the related table to make the relationship between the two tables.

...do I have to select all of the "key" fields to create a combined primary key?

I'm not sure what you mean above, but I think you may be referring to a composite key. This can be used if you bring in two foreign keys into one table to relate them (i.e. a junction table). The junction table would not have a formal primary key field but would have the two fields combined and the combined fields would describe a unique record. See the example below:


tblPeople
-pkPeopleID primary key, autonumber
-txtFname
-txtLName

tblTitles
-pkTitleID primary key, autonumber
-txtTitle

tblPeopleTitles
-fkPeopleID foreign key to tblPeople
-fkTitleID foreign key to tblTitle


I have seen a lot of discussion on other Access forums as to the advantages and disadvantages of composite keys. The one thing you have to remember is that if you have relationships built off a table with a composite key, both fields that make up the key must also be in the related tables. If you have a lot of relationships, this could get a little cumbersome. I personally have not used composite keys, so in the above case, I would structure the tblPeopleTitles with an autonumber primary key to unique identify the record. I would then use that as a foreign key in any tables that relate back to tblPeopleTitles.


tblPeopleTitles
-pkPeopleID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkTitleID foreign key to tblTitle

[TracyBell]

jzwp11 - Yes, composite key is what I meant - I just couldn't think of the proper name. That's good advice, I didn't realize I'd need to carry each one to any related tables.

I'm beginning to question whether I should put the tenants into the same person table as the owners and other contacts. The reason is that person table holds "permanent" address information for those people. Obviously the tenant's address is tied to the unit and could possibly change since a tenant could move from one unit to another. Do I just leave the address blank for the tenants or should I put them in a different table? Or would it be better to put the owner/contact addresses in a different table? I'm getting a little anxious about being able to tie things together in the reports when the pieces are in so many different places. Is that normal? I'm hoping you can ease my mind since you obviously know what you're talking about. :-)

[jzwp11]

tenants into the same person table as the owners and other contacts

Like data is usually put in the same table. As you describe, a tenant can have many addresses, but you also have another relationship in that an address can have many tenants. So you have a many-to-many relationship which needs a junction table that brings the tenant (people) together with the addresses.

Do I just leave the address blank for the tenants or should I put them in a different table?

Since 1 address can apply to many people, having the address in the people table won't work. You will need a table to hold the addresses and relate them as I have described above.

I'm getting a little anxious about being able to tie things together in the reports when the pieces are in so many different places. Is that normal?

Yes, it is normal for a normalized design. If you have everything properly structured, the queries are more straightforward then if you were using a de-normalized design.