Tables Setup Correctly (normalized?)

[AC5FF]

Okay, so I have an existing DB that was thrown together in a rush and done incorrectly. I am trying to recreate it from scratch, but I want to set this up correctly. So far I thought I was on the right track, but it is not operating as I would expect - or I am overlooking something that I hope someone here can point out to me.

Essentially, I am recording daily information on accounts. Originally all the information below was in one table; I've split that into 4 tables:

tblLOG
ID (primaryKey autonumber)
ReportDate
AccountNumber
Exception
Runtime
Avg
Send (y/n flag)

tblPROPERTY
PropertyID (primary key autonumber)
ID (to link to tblLOG)
cleared (y/n flag)
RespNotes

tblNOTES
NotesID (primary key autonumber)
ID (to link to tblLOG)
TechNotes

tblVACANT
VacantID (primary key autonumber)
ID (to link to tblLOG)
Vacant (Y/N)
Vdate

tblLOG will get 100 to 500 entries daily. Based off history, only a few (under 10) get flagged as Send. Anytime notes are received from a property or added by a tech they get added to that day's account number's link into the appropriate table.

As for the tblVACANT, thats just storing for me the last time an account was flagged as vacant (to ignore items in the tblLOG).

Like I mentioned, all these fields were in one table when I took over the DB. I'm pretty good at using what I am given, but I am not great at setting up a DB from scratch. Am I moving in the right direction?

 

[MarkK]

What is a "log" object? Typically a row in a table represents a thing in the real world, like a Customer or Order or Job or something. Every table, in this respect, is a log. What matters is what is being logged. And the fields in your log table don't really clarify the matter. What is being logged?

Also, what is your overall purpose?

 

[jdraw]

Further to Markk's comments, can you give us a 5 line overview of the business involved.
Just plain English, no jargon. Tell us like you'd tell a six year old.

...our company is in the NNNNNNN business.We deal with PPPPPP for whom we BBBBB.

Property management???
Vacant land, vacant houses?
Notes about what?

 

[AC5FF]

Mark: I called it tblLOG only because it's where i am logging all my daily information. tblLOG, tblPROPERTY, tblNOTES, tblVACANT are all table names - not field names.

Essentially I get a text file report daily that gives 500+ lines of information on accounts. That could be their use for the day (runtime) or a problem with the account (exception). The avg is also given for each account with usage. I run a couple queries to each account with runtime to flag (send) the account for further action that day.

JDraw; simple explanation - i'm managing 100+ properties down to each apartment level and watching for equipment problems (exceptions & usage).

 

[jdraw]

Ok, so you do some sort of property management which involves Accounts and Properties.
I can identfy vacant properties/houses/apartments, but am not sure what a vacant account is. Terminated? Inactive?

Does an Account represent 1 or more Properties?
Can a property be part of more than 1 Account?

Are/could the Notes be categorized - equipment, electrical, plumbing, heating, ....?

Maybe Markk understands Log, but I'm still scratching my head. Is the main purpose of the Log to represent Account activity?
What exactly is (runtime) -- that has a meaning within Access but I don't think that's your usage.

 

[AC5FF]

I'm pretty bad at explaining things eh? LoL
Each account number = 1 address; I.E. Apt 101 at Complex X
Accounts never change; it's like the physical address for the apartment. There are other DB's that connect names to accounts; but those are not part of my DB.

I do however link in other tables that I can equate an account number to a physical address/phone/etc - I just didn't list that above because I considered it ancillary to my setup.

Can notes be categorized? To an extent. Only that I have notes from properties and notes from technicians. I plan to use a part of the technicians notes as a basis for ignoring items in the tblLOG.

As for understanding Log - feel free to replace "LOG" with "REPORT" or whatever. Basically, I take a text file with a list of problems reported each day and import that text file into this "LOG" table. I called it "LOG" only because it is logging every line of every daily report.

For a quick example. One line of data in this LOG could look like:
21816 - 4/14/2016 - 12A31C8 - H - 280 - 18 - Y
Where:
21816 = ID
4/14/2016 = ReportDate
12A31C8 = AcctNumber
H = Exception
280 = Runtime
18 = Avg
Y = Send

 

[jdraw]

That's making things a little clearer.

What are Exceptions and runtime, maybe some examples would help.

It seems you are iin the process of redesigning/redeveloping this database from the original 1 table set up.

 

[AC5FF]

Exceptions = One Letter codes that tell me what may be going on with one account.
For example: H ID's it as high usage, Z is for a Zero, B is battery, etc..
Runtime; will just be exactly that, a number that will equate to usage.

Yes, I am redesigning from original 1 table setup.

What I thought I could do here would be my original tblLOG table that would look like this.

I am pretty sure I can set this up into a form to do with queries, but reading through other examples I saw this as the 'correct' way to set up a database. Maybe I'm wrong?!?

 

[jdraw]

For example: H ID's it as high usage, Z is for a Zero, B is battery, etc..
Runtime; will just be exactly that, a number that will equate to usage.

This could be a separate table if normalized.

Let's go with tblException

ExceptionId PK
ExceptionCd eg "H"
ExceptionDesc "high usage...."


Still not sure what runtime is in your terms. Seems to be usage in hours?
Could be start/stop times??
The exceptionID would be a foreign key in the associated table.


In order to determine if your database is "normalized", you need to know what the business and the rules are. You can make a model and test that model with some test data. You use some test scenarios and work them through the model -resolving/reconciling all issues that arise.
When it's all working, you now have a blueprint for the database structure.

It seems that your database is a part of a larger picture. So, it's a little difficult to get really detailed not knowing exactly what is in scope.

Here is a free video by Dr Daniel Soper that may help/focus on some concepts re database and table structure.

 

[AC5FF]

Thanks. .I'll look at that video. got an hr of my day for the next few days taken care of going through these vids!

 

[jdraw]

Here is a very rough draft based on this thread.

Ideally you should describe your business, then describe each of the hings involved such that there is no confusion that an A is an A and not sometimes a B etc. It's amazing what happens where you go through that exercise and bounce it off some colleagues.

As you improve the description of the business, and the rules you will be able to revise/adjust/create a model to support your business requirements.