A Table Relationships Problem.

[TNH]

Hello,

I was hoping someone here could help me with a little (maybe conceptual) difficulty:

I am creating a database to help with the running of a club I am a member of. The particular issue I have surrounds a table called "Expenses", where I intend to store information about any purchases and expenses the club makes - Date of expenditure, cost, who signed the cheque etc. These expenses come in different catagories, such as insurance policies, consumables or equipment services. I naturally wish to store information about insurance policies in a separate table (to store policy number, insurer, value insured, items insured, expiry date etc), and the information on equipment services in a further table (Service date, serviced by, comments on repairs, certificate validation etc), and so on through all of the different types of expenditure I need.

I have tried to create a field 'Expense ID' in each of 'Insurance Policies', 'Services' etc, and a relationship between each of these and the primary key in the 'Expenses' table. I don't want to make this 'Expense ID' field in the catagory tables a primary key (Not all items in these tables are expenses), however there will be no multiple references in these relationships, suggesting I should use a 1-1 relationship.

I also want to be able to bring up the details of each record in 'Expenses' using a sub-datasheet, but naturally the source for this sub-datasheet will depend on the expense catagory. Is there a way of overcoming this?

It may also be obvious that I am not an expert at relational databases, so any constructive critism of the way I have gone about this design is gratefully recieved.

Thanks for any replies

Tim

 

[FoFa]

If I understand correctly (and I may not) May I suggest something like this:
Expense Category Table, that would contain the type of expense so you can use it in a drop down to select from (this would be where you would identify Insurance, Kick Back, etc type of expense).
Expense Table, which contains basic info. about the expense to include the category (use a foreign key, like an autonumber to tie to your category table).
Expense Details Table which can contain (Ta Da) the details of the expense. It would use a foreign key to tie to the Expense table. Also you could have more than one detail record for an expense (say multiple payments or something like that).

Just a thought

 

[TNH]

Thanks for the reply FoFa.

Sorry about my question being incomprehensible, I have a knack of writing in a way such that only I know what I'm trying to say - not always helpful!

What you suggest sounds workable, but what I am really after is a way of keeping details of the expenses in different tables so that I can sotre the information relavent to that catagory (each table would have different fields), and manipulate the data in different ways. As I read it, your suggestion doesn't allow me to do that. However I really like the multiple payments idea, thank you.

 

[FoFa]

Actually you can include or exclude different categories using this setup since the Expense table would be joined to the category table. Your query could select all the expenses for Category "bribe" as an example and pull the related expense and payment records.

Category <-> Expenses
Expenses <-> Payments

SELECT * from Payments as P inner join expenses as E on E.EID = P.EID inner join Category as C on C.CID = E.CID where C.Category_Name = 'Bribe'

Would pull all the information in payments for all Bribe expenses.
Given CID = UniqueID for Category, EID = UniqueID for Expense

Of course by adding to the WHERE clause you can limit to dates or whatever.
Just a thought.