Do I need more tables? 7

[curtterp]

I have built a small but effective database to track my bills, both due and past due. I have two tables at the present, main and accounts. Accounts is just a table of my creditors, account info, address's of accounts etc. Main holds the bills due, paid, amounts. I am looking to expand the database to include total amounts due(credit cards, morgage, etc). do I need a table for each creditor to use math functions, enter interest amounts, and other stuff like that? or can I do that from the main table were I have all other functions. I basicall have done flat files for a few years in Filemaker pro on Macs, and I am real green on relational databases, and access.

I do not do this for a living, but like to do stuff for my personal use, and thats how I learn the this stuff. Any suggestions are extremely welcome at this point. Thank you ;-)

 

[JerryDennison]

Are the two tables you currently have related? What does the table structure of the main table look like? I suspect you're still treating it as a flat file db. If so, you really should consider learning more about relational db structure. You'll find it far superior to the flat file schema. Relational db dramatically reduces redundant data and vastly improves db efficiency.

 

[khood]

curtterp:

Relational databases allow you to easily relate tables using key fields, such as account#. There are several factors that you should use to determine if you need to store information in separate tables:

1) If there is a one-to-many relationship. Example: You have one Visa Card account (one account record), but you can have many transactions (purchases, payments, fees, whatever. In this case, you should have a least two tables, one for the accounts and one for the transactions. The tables would be related by the account#.

2) If there is "auxiliary" information that may or may not be necessary for every account record. Example:You may have some bills in which you have made a change, or disputed a charge. You could create a table for notes, again related to the account table by account#. There may also be plurality in the notes as well - another reason not to store it in the account table.

3) Master codes - codes that are used to simplify your data entry. Example: For every account, you probably have an account type field (credit card, utility, auto loan, etc.), and you certainly have state codes. You wouldn't want to type this in every time and risk miskeying the information - store it in a separate table & use a combobox control to select it. This is the best way to standardize your input & keep your data clean.

I can give you more examples, but I don't want to write a book here. Just remember - the best relational database design does not duplicate fields (normalized), but relates tables based on key information.

Hope this helps.

Ken Hood

 

[JerryDennison]

Ah, the best one can hope for is to be "normal".
;-)

Always remember, a descriptor must describe the key the whole key and nothing but the key.

 

[curtterp]

Jerry,

I have the two related somewhat. I have a pull down list when I enter the new bill that is due, it pulls from the account table. The main table holds the amount, date due, amount/date paid, has a field for check box when paid ( that helps with a couple of queries that I have) also have a field for total amount due and a transaction number. Yes it still is somewhat a flat file. I tried to learn the relationship with Filemaker pro 3, but could never grab the concept with that program. I am sure if I took a couple of classes, then I could get the concepts.

BTW, the forum here is great. I already figured out how to pull 2 different queries on one printout, so my bills due and over due print on one page.

Thank you for responding

Curt Terpstra

 

[curtterp]

Mr Hood,

Your information is helpful.... it has given me ideas on how to possibly structure the database. Some of what you said is a little over my head right now, but I do really appreciate the input. It has given me food for thought

Thank you

 

[khood]

curtterp:

Thanks. There's lots of examples on the internet, including many complete databases that will give you ideas. The sample databases included with Access can also give you an idea of how to get started.

Good luck,
Ken Hood

 

[JerryDennison]

You can "normalize" your existing data. One of the best ways to get started building fairly normalized databases(without fully understanding the concept or knowing all the Forms of Data Normalization) is to "build" your db on paper first. The hardest part is understanding ENTITIES and DESCRIPTORS.

I like to think of an entity as something you can hold in your hand(if your hand were big enough). In other words, an entity is a physical(or logical) thing that cannot be broken down into smaller components without permanently damaging it.

A descriptor describes some attribute about the entity. In database terms an entity is a table and a descriptor is a field.

Since you're already familiar with flat file databases you know that rows are records and columns are fields in that record.

Relationships exist between entities. There are two types of relationships. Independent and dependent. An entity is said to be dependent if it cannot exist without the parent(exactly as a child cannot exist without parents). Independent relationships now speak for themselves. This is one of the more difficult concepts to grasp and probably the most widely violated.

Here are some good steps to follow for designing your database. List every piece of information you want to track. Be very descriptive about each. For example, I want to track all my friends vitals(name, address, etc.) and the beer(s) they like to drink.

Friend First Name
Friend Last Name
Friend Address
Friend City
...

Beer Brand
Beer Manufacturer
...

Friend Beer Brand
Friend Beer Pints consumed per day
...

Notice how things are beginning to line out. Start grouping these logical entities together and ask yourself if each can stand on it's own and how it's related to something else. You'll start seeing the patterns of entity and descriptor and relationship form.

 

[MichaelRed]

Jerry,

Ah, the best one can hope for is to be "normal".

.... but not TO Normal (never beyond the third form?))




MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[JerryDennison]

At least up to and including the Third Normal Form.
;-)

 

[JerryDennison]

There is one important thing I left out.

Primary Key

This is undoubtedly the most misunderstood and severely abused concept in RDBMS development. On the one hand way too much emphasis is placed on its relevance and on the other hand not enough emphasis is placed on its roll.

The primary key is a way for to identify the entity. BUT ONLY AS IT RELATES TO DATABASE PROCESSES! This means, the primary key is a way for Access to find a specific record for a given entity. Other than that it has no meaning. It is not a way for the user to find the entity, that's what descriptors are for. As such, the user need never even know the primary key exists. For all of these reasons that is why autonumber fields make such good primary keys. What the actual number is has no meaning other than it be unique to the record it is for. As such, it doesn't matter if there are "gaps" between records when it comes to their PK.

Compound Primary Keys:
Another highly abused concept and one that Access doesn't actually support(yes you can select multiple fields and assign them as a primary key, but just try searching for and finding records using this compound key sometime). Compound keys are very useful when it is necessary to insure that two or more fields cannot contain the same combination of values in more than one record. I use them myself on occasion as it is far easier than trying to build the necessary code to do this. BUT, I do not try to use this as the true PK. I add a psuedo primary key(via autonumber) field which is used just like any other pk(that is, I use this field to link to those tables that are related). There is one drawback, you can't enforce referential integrity or cascade updates/deletes. But it sure does improve performance and actually makes it possible to search compound pk tables.