Normalization & Relationships (1 Viewer)

ZenDiagram · Sep 20, 2018

I built a database that works pretty well but I'm realizing that my original db table design & relationships were not designed well.

I am going back to the basics. I would love some feedback.

purpose of the database:
keeping track of land owners in a town who have one or more properties under a certain tax classification.
reports generated from this db are used as applications. Each land owner must reapply every year for this tax status. I print the applications with each owner's mailing info and info for each of the properties they own, send it to them, and they return it with any changes to the information- which I then update in the db.

I have a preliminary table hierarchy outlined with photo attached.
tbl_Owners-->tbl_Properties--->tbl_Fiscal Year

Tbl_Owner: Active (Yes/No), Owner first name, owner last name, mailing street #, mailing street name, Phone #, etc.
Tbl_Properties: Property street #, property street name, total Acres, etc.

Tbl_Fiscal Year: This goes on the application (access report) and I'm not sure how to incorporate it into the tables. Applications are sent out once a year. This year we would print applications for fiscal year 2020. So it changes every year.

How should connect these tables?

Thanks!

GinaWhipp · Sep 20, 2018

Hmm, so going back to basics...

1. Naming Conventions - you're going to want to remove the spaces and wildcard characters from the field names and table names, see link below...
https://www.access-diva.com/d1.html

2. I am assuming an Owner can have more than Property. You're going to need a new table...

tblOwnersProperties

3. Not sure you need Fiscal Dates in a separate table as you can gleam that via a field in the Accounts table depending on what you are using that for.

plog · Sep 20, 2018

The general rule is that every noun you use in describing your data should be a table. Then, all the attributes of those nouns become fields. Further, when speaking with people unfamiliar with your industry you shouldn't use synonyms.

You used these nouns in your description: Owners, Properties, Applications, Tax Status. I don't see any table nor field that relate to Applications nor Tax Status. Also, you have an Account table in your tables but no use of that term in your description.

I suggest you give your description and relationship diagram another attempt, making their names jive and accomodating everything you talk about. Then post a screenshot (making sure to expand your tables so we can see all the fields) and then post back with any questions you haave regarding the relationships you created.

ZenDiagram · Sep 20, 2018

Ok, I have renamed the tables.
Here is updated version (attached photo)

ZenDiagram · Sep 20, 2018

plog said:
The general rule is that every noun you use in describing your data should be a table. Then, all the attributes of those nouns become fields. Further, when speaking with people unfamiliar with your industry you shouldn't use synonyms.

You used these nouns in your description: Owners, Properties, Applications, Tax Status. I don't see any table nor field that relate to Applications nor Tax Status. Also, you have an Account table in your tables but no use of that term in your description.

I suggest you give your description and relationship diagram another attempt, making their names jive and accomodating everything you talk about. Then post a screenshot (making sure to expand your tables so we can see all the fields) and then post back with any questions you haave regarding the relationships you created.

That's good advice.
So- I need two basic tables: TblOwners, TblOwnerProperties.

You will see in TblOwnerProperties the fields named "61","61A","61B". Those are the different tax statuses. A single property could have acres in any or all of them. Not sure if this is helpful to you or unnecessary info...

When I mentioned applications I meant that the database is used to create reports - those reports are printed out and physically sent out to the Owners.
We just call them "applications". They are not incorporated into the tables at all.

My questions are:
Am I relating these tables correctly?
Where should Fiscal Year go? Its only purpose is to go on the report to show which year the reports are for. The fiscal year will change every year. It is not related to the tblOwners or tblOwnerProperties in any way I can see.

See attached photo

(disregard field Forestry ID - i am deleting that field)

GinaWhipp · Sep 20, 2018

Tables are fine but what about those field names? Take my word for it you are going to want to get rid of the spaces and special characters they are just going to create additional work once you start the development phase.

As for Tax Status' I would break that out (but that's optional) and place it...

tblPropertyTaxStatus

But that's just me because I hate tables set up like spreadsheets.

plog · Sep 20, 2018

As Gina said--no need for a FiscalYear table. You are using a number to relate a different number. Just use the actual FiscalYear value instead of its ID and get rid of that table.

I don't think it goes in tblOwners anyway. Nor does it go in Proprities. A FiscalYear is not an attribute of either of those. You're a little too close to your data at this point. Step back and for each field ask 'What is this an attribute of?' The answer to that is what table the field goes into.

My guess is a FiscalYear is an attribute of a tax status for a property. That means it goes into some sort of junction table (https://en.wikipedia.org/wiki/Associative_entity). That type of table defines a many-many relationship between tables.

Also, you don't use actual values of your database in names. That means 61, 61A & 61B should not be field names, but field values. You should have a field called [TaxStatus] in some table which will hold those values. My guess is you need a table like this:

PropertyTaxStatuses
pts_ID, autonumber primary key of table
ID_Property, number, foreign key to tblOwnerProperites.PropertyID
pts_FiscalYear, number, will hold fiscal year tax status is for
pts_Status, text, will hold the values 61, 61A & 61B

That table then tells you what property is in what status for what year. That table might have another field to hold whatever value you are storing in the existing 61, 61A & 61B fields. What are you storing there? Number of acres in that status?

mike60smart · Sep 20, 2018

Hi

I take it that one property will have 1 Tax Status?

If this is the case then you just need field named "TaxStatusID" - Number Datatype.

Then you need a table which would contain a list of all the Tax Status's

tbluTaxStatus
-TaxStatusID - Autonumber PK - No Dups
-TaxStatus - Text (List of Status ie 61, 61A,61B)

ZenDiagram · Sep 20, 2018

OK - Are these field names looking better?

ZenDiagram · Sep 20, 2018

mike60smart said:
Hi

I take it that one property will have 1 Tax Status?

If this is the case then you just need field named "TaxStatusID" - Number Datatype.

Then you need a table which would contain a list of all the Tax Status's

tbluTaxStatus
-TaxStatusID - Autonumber PK - No Dups
-TaxStatus - Text (List of Status ie 61, 61A,61B)

No, a property could have one or more tax statuses.

mike60smart · Sep 20, 2018

Hi

OK Then Plog has covered this scenario

ZenDiagram · Sep 20, 2018

plog said:
As Gina said--no need for a FiscalYear table. You are using a number to relate a different number. Just use the actual FiscalYear value instead of its ID and get rid of that table.

I don't think it goes in tblOwners anyway. Nor does it go in Proprities. A FiscalYear is not an attribute of either of those. You're a little too close to your data at this point. Step back and for each field ask 'What is this an attribute of?' The answer to that is what table the field goes into.

My guess is a FiscalYear is an attribute of a tax status for a property. That means it goes into some sort of junction table (https://en.wikipedia.org/wiki/Associative_entity). That type of table defines a many-many relationship between tables.

Also, you don't use actual values of your database in names. That means 61, 61A & 61B should not be field names, but field values. You should have a field called [TaxStatus] in some table which will hold those values. My guess is you need a table like this:

PropertyTaxStatuses
pts_ID, autonumber primary key of table
ID_Property, number, foreign key to tblOwnerProperites.PropertyID
pts_FiscalYear, number, will hold fiscal year tax status is for
pts_Status, text, will hold the values 61, 61A & 61B

That table then tells you what property is in what status for what year. That table might have another field to hold whatever value you are storing in the existing 61, 61A & 61B fields. What are you storing there? Number of acres in that status?

Ok - goods points about the fiscal year.
Yes- I am storing number of acres for each tax status in those fields.

the field [land classification] is where I put the values 61, 61A, or 61B. SO I could rename [land classification] as [tax status] because they are the same thing. (sorry, synonym)

The only purpose of this field is to sort the reports by each type of tax status.
It would take too long to explain why I have acres in each tax status as well as an overarching field for tax status.

plog · Sep 20, 2018

Then that means PropertyTaxStatuses gets a new field: pts_Acres

GinaWhipp · Sep 20, 2018

ZenDiagram said:
OK - Are these field names looking better?

Hmm, well you'll have to live with the spaces in the field names. Just remember done the road bracketing will be required.

Cronk · Sep 21, 2018

Re #5

No, a property could have one or more tax statuses.

That means another table, not multiple fields. Otherwise, you will not have a normalized tables.

Also I presume you need to provide for sales of properties. That would need a table with

OwnerID
PropertyID
FY

This table could hold the Application details

Pat Hartman · Sep 21, 2018

The field names still have spaces in them. Object names should contain only lower/upper case letters, numbers, and the underscore. Access is very flexible in what it allows you to use as object names. However, VBA is a programming language and they are not flexible at all. Best to just get rid of all the offending characters. You still have / and # as well as the embedded spaces.

Tax liens belong in a separate table since any property may have multiple liens at any time.

Tax status belongs in a separate table also. You never want to get yourself boxed into being limited to 3 liens or 4 children or 2 owners. Any time you have more than 1 of something, you have many and many means you need an additional table. So the owners also belong in a separate table especially since the owners can have different addresses and contact info.

ZenDiagram · Sep 21, 2018

Ok - Thank you everyone I am trying to incorporate your replies.
I have attached a sample database. Could someone go in an further normalize the tables?
I am having a hard time envisioning this.

ZenDiagram · Sep 21, 2018

Also- I am only making a field for the most recent lien.
Each property only has one lien and that is the most recent lien recorded for that property.

mike60smart · Sep 21, 2018

Hi

Your Diagram should look like this

plog · Sep 21, 2018

tblOwnerProperties
--61, 61A and 61B should not be fields. They are values that are stored inside a generically named field.

tblOwners
--Why is the FiscalYear in this? What is the fiscal year an attribute of? Hardly seems that an owner has a fiscal year. Most likely that is attached to a tax status
--A lot of this data is duplicated in tblOwnerContacts. You should remove the contact information (OwnerFirstName, SecondOwnderLastNameBusiness, CellNo, etc.) from this table and store it in tblOwnerContacts. Most likely that will mean adding a [Type] field to tblOwnerContacts so you can designate what role they play in ownership.

Also, next time you do this, simply set up your relationship tool and post a screenshot of it.

Normalization & Relationships (1 Viewer)

Registered User.

Attachments

AWF VIP

Banishment Pending

Registered User.

Attachments

Registered User.

Attachments

AWF VIP

Banishment Pending

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Banishment Pending

AWF VIP

Registered User.

Super Moderator

Registered User.

Attachments

Registered User.

Registered User.

Banishment Pending

Similar threads

Users who are viewing this thread