Normalization & Relationships (1 Viewer)

Okay, take a look at this:

Note: there is only one owner and that one owner's mailing address that the report is mailed too. But there could be other owners that are associated with the property(ies).

there is only one owner...But there could be other owners

Click to expand...

All their data goes together. Again, if you need to designate types of owners, add a new field to OwnerContacts.

You are still storing values in field names. 61, 61A, 61B should never appear in the relationship. Those go in fields as values, not in field names.

No need for tables with one real field for data (autonumbers aren't real data, they are meta data used by the database). Get rid of tblFiscalYears and tblClassifications and just store their values in tblPropertyTaxClassification.

"You are still storing values in field names. 61, 61A, 61B should never appear in the relationship. Those go in fields as values, not in field names."

the 61, 61A, 61B are not the values- those fields are where I store the acres.


Here is another version.

Also - I should have been clearer: For this database there is an owner 1 and an Owner 2 . That is all that's allowed.

61, 61A and 61B very much are values. They are types and should go into your table as such. This is what that table should look like:

tblPropertyTaxClassification
ptc_ID, autonumber, primary key
ID_Property, number, foreign key to tblOwnderProperties.PropertyID
ptc_FiscalYear, number, fiscal year value
ptc_Classification, ?, will hold whatever Classification now holds
ptc_Acres, number, will hold number of acres
ptc_Type, text, will hold the type values of 61, 61A, 61B


Then instead of 3 fields for the 61, 61A, 61B data, you will have 3 records (one for each type, assuming they have a value for that type).

Hi

I forgot to remove the fields 61, 61a, 61b from the property table

Here with the modified diagram:-

plog said:

61, 61A and 61B very much are values. They are types and should go into your table as such. This is what that table should look like:

tblPropertyTaxClassification
ptc_ID, autonumber, primary key
ID_Property, number, foreign key to tblOwnderProperties.PropertyID
ptc_FiscalYear, number, fiscal year value
ptc_Classification, ?, will hold whatever Classification now holds
ptc_Acres, number, will hold number of acres
ptc_Type, text, will hold the type values of 61, 61A, 61B


Then instead of 3 fields for the 61, 61A, 61B data, you will have 3 records (one for each type, assuming they have a value for that type).

Click to expand...

Ok I have made a slight compromise on this. See photo.
Fiscal year is going in tblOwnerProperties. It doesn't really matter where it goes so I'll just keep it there. it's only use is on the report. I could just make a label on the report but then i would have to change it every year vs. just doing a query update.

I feel like we are almost there. Thanks so you much for your help so far!
Please give feedback on my field names. I made them more specific.

One step forward and 3 steps back.

You had a property table where the property details like address and area don't change from one FY to the next. In your tblOwnerProperties you will be recording this data every FY

Your tblAccounts has owner information. Why not call it by the entity name tblOwners?

Plog has raised the matter of the storing the 61 type areas in a separate table, as would be required to satisfy proper normalization.

I also raised the matter of property sales in #15. I presume Deed information would change on sale. You don't want to keep a history of previous property owners?

zen,
You are having a lot of trouble following the suggestions. Perhaps a time out to study normalization will help.

I am new to Access and get overwhelmed with the details, sorry if it seemed like I wasn't taking your suggestions.
I have studied normalization, but most examples are given for companies that sell things. I know the logic is the same but I have a hard time visualizing normalization for my database since it's content and purpose is different from all the examples I see.

You'll see in my latest attachment that I have made updates based on the suggestions you gave.

- Fiscal Year is in tblOwnerProperties since I will be updating that data every year.

- The 61 types are in their own table

- I have renamed tables and fields based on suggestions. (I'm not sure when to start a field name with the abbreviated table name i.e. (prop_StreetName or ptc_acres) etc. Any rules for that?

- I made a Owner Contacts table and made an owner_type field.

- re: deeds- No, I don't want to keep a history of previous owners. I know I could, but I use the Registry of Deeds website for that.

- Each year I plan on backing up the database and saving it for that year. That is how I plan to archive changes made each year. Maybe I could get more technical with saving things within the database by fiscal year but I don't want to get that technical right now.

Thanks.

Your structure looks good---with the exception of Fiscal Year which I believe belongs in tblPropertyTaxClassification. With that one change you should be able to avoid your annual copying plan which is based on misguided logic. Placing FiscalYear properly will allow you not to have to do anything to have this database work year in and year out. A database isn't a spreadsheet you copy every year, blow away a tab of data and rename with the year in the new file name. A database should have no expiration date.

My suggestion now is you test your structure. Put some sample data (perhaps from prior years) into your database. First, make sure it accomodates all your data. Second, make sure you can get your data out like you need. That usually means building the reports you want and the queries to support them.

Hi

The only comments I have are as follows:-

1. You should change the Joins between tables so that Referential Integrity is Enforced,
2. In tblOwnerContacts change the PK "ID" to "OwnerContactID"
3. In tblOwnerContacts change the field Owner_Type to "Owner_TypeID" This indicates that you will be storing the PK of the OwnerType you select from tblOwnerTytpes (This is assuming that you have created a table that lists all of the Owner Types?)
4. In tblOwners change the field MailingState to "MailingStateID".This indicates that you will be storing the PK of the State you select from tblStates (This is assuming that you have created a table that lists all of the States?)
5. In tblPropertyTaxClassification. Change the field ptc_AcreType to "ptc_AcreTypeID". This indicates that you will be storing the PK of the AcreTypeyou select from tblAcreTypes (This is assuming that you have created a table that lists all of the AcreTypes?)

I still see confusion.
1.The mailing addresses for the owners belong in tblOwnerContacts which eliminates the need for tblOwners and changes the FK in tblOwnerContacts from OwnerID to PropertyID. Each owner has his own contact information. If you want to designate one owner as the primary, there are ways to do that but if all owners are created equal, then just enter each as a row in the contacts table.
2. OwnerID does not belong in OwnerProperties. PropertyID belongs in tblOwnerContacts (or tblOwners if it turns out that there is a need for this table). Think about it this way, If you need multiple IDs (which you would if you needed to reference each owner from the property), then you are defining the relationship backwards.
3. Prefixing all attribute names with a fixed string of characters doesn't do much for you except cause you more typing until intellisense kicks in. If you really want this string to tie columns to a table, a suffix is actually much more useful and serves the same purpose. I don't use either. If you decide you want to use a prefix or suffix, be CONSISTENT. Always use one or the other and always separate using an _ or never separate.
4. Please consider what I said about lien's They don't belong in the properties table since there can be more than one of them.
5. If 61_grossSales is a summary, then remove it and put the details in the taxClassification table. Also be careful about not including data (61) in a column name. I once spent three days when I was a trainee trying to track down a bug because my stupid predecessor made a change to the application and code that used to apply only to Dept88 (that was the name of the field) was changed to also include Dept49. She was too lazy to rename the variable and I was too green to know that people were this sloppy.
6. There are still embedded spaces.
7. Consistency is very immportant. When you use "_" to separate parts of a name, don't vacillate. Either always use them or never use them or only use them for emphasis.

I don't know what this application is supposed to manage so I don't know how to advise on some other issues. For example, a property could change tax classification over time. A property could have different owners over time. An owner could have different mailing addresses over time or different phone numbers. Is any of this important to you or is all you care about the current value? If last year there were three co-owners of a building and this year there are two, would you just delete the third owner data?