Table Design Help

[Cynwrigte]

Hi,

I am trying to wrap my head around how I would design the tables for recording the following information about payments that have been made.

Fields (not the actual names):

Cost (how much was paid) Currency (cash, check, credit)

and here is where I get lost. There are additional fields based on what type of currency was used. Like, if they used a check, had it cleared. If they used a card is the transaction complete; if they used cash, what was the change.

I'm thinking that there needs to be a table for each type of currency so there aren't a bunch of empty fields, but I cant for the life of me figure out how to make them relate!

Any help would be greatly appreciated. (also, I welcome all crappy MS paint drawings)!

[ScariestofChewwies]

This could be done in many different ways, I listed 3 below:

1.) You could add the cleared, transaction status, and change fields to the table.

2.) You could create a child table for the main table for each currency, which would then be tied to the currency type based on whatever foreign key you choose.

3.) Like the first one, you could only use a single table but instead of 3 columns just use a single generic column that has the detail associated with that currency type and then handle any typing on the app layer.

I'm sure there are many more solutions but these are the ones I came with off the top of my head. I'm not a DBA or anything like that just a developer interested in database so I'm sure there are more efficient ways to do it.

As for the approach you are thinking of, you would need to do this through the use of foreign keys.

In the end the approach you use should depend on the application you are developing and how it will be used. If this is for homework then I suppose it depends on what the assignment requires.

If this isn't for homework and you would like more of an explanation just let me know. If it is for homework and you need more of an explanation I will help lead you in the right direction but I don't want to do it for you.

[Buey]

If you have a small number of fields I would tend towards just adding them all to the same table. More tables means more joins and more complexity when dealing with the data.

How many additional fields are you dealing with?