My First Database (design is confusing me) (1 Viewer)

[Cwwrig]

I'm developing my own personal finance database and the part stumping me on the design is how to setup the categories per transaction. For example a primary category would be "home" and the secondary category would be "furnishings". How do I set this up? I'm getting stuck on how to tie it into the transaction table.

I'm familiar with writing SQL queries, but this is really my first stab at database design.

The relationship diagram is attached. The transaction table and the category tables are to the right of the diagram.

Any input is greatly appreciated.

 

[Cwwrig]

I'm about to walk away because my brain is going in circles, but here is the next shot at me trying to solve my problem. The issue is the transaction table needs to have a primary and secondary category, but the combination needs to be unique. For example if I select "Home" then I only want the secondary categories available in the Primary_Secondary table to be available. I don't get how to force that in the transaction table. The way it is setup now would basically just let me select from the full list of primary and then secondary tables. I'm stuck on how to get the Primary_Secondary table combinations to be considered within the transaction table.

I'm now walking away from the computer...

 

[plog]

First, why do you have two instances of the Account_Type table linked to [Account].[Account Type]?

As for your issue, I don't think you need to store the Primary Category in Transactions, just the Secondary Category. From an input perspective, your form would have an unbound drop down (one that doesn't get stored anywhere) of primary categories, and when one is selected it would limit the bound drop (one that does get stored) of Secondary Categories to just those of that primary category.

 

[stopher]

would it be true to say that any given secondary category only relates to one primary category? If this is the case then you only need to have the secondary category in your transaction table.
However, if transaction can involve any specified choice of primary and secondary, then you can index your junction table (the one that defines the acceptable list), and simple reference the index of this table in your transaction table.
hth
Chris

 

[Cwwrig]

First, why do you have two instances of the Account_Type table linked to [Account].[Account Type]?

- I noticed that as well. I think it is happening because I used the lookup wizard to get values and I'm assuming that process puts its own join in there (to show where the values are coming from). It is to make a drop down list possible.

Thank you for the replies. I'll look back over this and repost if needed. I need to think about this a little more. I think I get what you all are saying. I suppose it is highly unlikely that a secondary category would be assigned to more than one primary category.

 

[Cwwrig]

Alright, I must admit that I could not fully grasp on how to implement the suggestions. Also it is possible to have more than one secondary category assigned to a primary category. Such as Home - General, and Entertainment - General.

I came up with an idea to query the many to many table (where primary and secondary are combined). That query concatenates the two columns primary and secondary. I added this to the entity relationship diagram and joined to it although I was not able to enforce integrity (it was greyed out).

Is this a viable solution to my problem or will I run into problems with this approach?

Thanks for the advice!

 

[GinaWhipp]

Perhaps there will be something in one of the links that will help...

Sample data models...
http://www.databasedev.co.uk/table-of-contents.html
http://www.databaseanswers.org/data_models/

 

[plog]

Is it possible for multiple categories to have the same secondary category?

Category, Secondary
General, Entertainment
Children, Entertainment
Adults, Entertainment

In general, a table shouldn't hold just one field. If you don't have more related data, a table isn't necessary.

Also, you really need to clean up your names. In the table Account you have a field called Type as well as a field called Account Type. Account Type links to the Account Category field of the Account Type table. Very confusing. And that's not even mentioning the fact you have a few tables containing some sort of category that isn't related to either of those tables at all. Another example is that the Account and Transactions tables are linked and they both have Company fields, but they aren't linked that way, they are linked ID to Company.

Lastly, you have Alias fields in two tables that are related (Account and Transaction), if they hold the same information then the one in Transactions is unnecessary. If not, more confusing naming issue.

Use prefixes to help differentiate fields of the same names but for different data (i.e. TransactionAlias, AccountAlias.

 

[Cwwrig]

I hear you on the names. I need to go back and clean it up more. To answer your question yes a primary category can have more than one secondary category.

I would just build a table that has this for one column "Home - General" but the issue with that is it is more difficult to isolate "home" in a query, it is better if it is two queries. That way I would not have to use a LIKE statement to query it.

I think the query I added to the diagram will solve the issue, but like you mentioned I need to go back and clean this up more.

Thanks for the input.

 

[plog]

yes a primary category can have more than one secondary category

That wasn't my question at all. My question was:

Is it possible for multiple categories to have the same secondary category?

 

[Cwwrig]

Alright, so I did some reading and came up with a new game plan (better layout I think). The way to get a primary key/secondary key combination to appear is to have a secondary combo box based upon the primary key selection. The secondary spend category combo box will have an "update after event" that will query the junction table (where the primary & secondary spend category keys are stored). Only the values associated with the primary key will be available.

My new diagram is attached.