Normalization & Relationships

[plog]

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

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.

 

[ZenDiagram]

"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.

 

[plog]

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).

 

[mike60smart]

Hi

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

Here with the modified diagram:-

 

[ZenDiagram]

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).

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.

 

[Cronk]

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?

 

[ZenDiagram]

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.

 

[plog]

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.

 

[mike60smart]

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?)