Designing a SQL Database For Predetermined Accounts And Possible Write-ins

[alkos333]

I'm trying figure out the best way to deal with the following situations:

This scenario is oversimplified for the sake of the question. Let's say I have 3 tables: Transactions, Accounts and Designations.

Transactions table has an primary key called Trans_TransId and keeps track of all contact information associated with a transaction.

Accounts table contains a list of predefined accounts. It has a primary key called Acc_Code and a description column named Acc_Val

Designations table is a relations table which consists of a Trans_TransId, referencing a primary key from Transactions table, Acc_Code referencing a primary key from Accounts table, and Des_Amount which is an mount designated to a particular account for that transaction. This table allows for multiple designations to different accounts to be made per transaction (Trans_TransId).

This design accommodates situations in which the user uses only predefined accounts from the list. However, I also need to accommodate a situation in which the user would like to designate an amount to a certain account that's not in the the Accounts table and for which he/she will provide a description via a text box.

In your opinion, what's the best way to design this database in order to accommodate for those custom accounts?

[NeoPa]

For the integrity of your overall data, I would suggest maintaining all this data in the tables anyway. Certain elements may appear transitory and unimportant at the time, but the underlying data will not be inherently referential if transactions are stored pertaining to accounts that are not.

I suspect you may nevertheless need to consider the more permanent accounts differently. In that case you can introduce a flag into the table to indicate whether the account is considered permanent or transitory.

If the data isn't stored you get problems where a report/query including the transactions alone returns values whose total is A, yet a report/query on the same data, but including the linked Account information, returns a value of B.

[alkos333]

I can't maintain all of these accounts as there are way too many and we don't want all of them to be displayed for the user to choose from. We do want to give them an option to write their own in that we can late manually match to an appropriate account. I just want to figure out the best way to deal with these write-ins.

[NeoPa]

alkos333:
I can't maintain all of these accounts as there are way too many ...

Too many for what. I find it hard to conceive of there being too many for SQL Server to handle.

alkos333:
... and we don't want all of them to be displayed for the user to choose from.

That can easily be handled by the flag to differentiate those accounts which are official and those that you refer to as write-ins.

alkos333:
We do want to give them an option to write their own in, that we can later manually match to an appropriate account. I just want to figure out the best way to deal with these write-ins.

Excuse my rewriting your quote. I felt it would help to understand what you were trying to say.

This is new information of course. I think, on reflection, I would still recommend the same approach, but if you are doing later manual matching, then any temporary accounts that no longer have anything tied to them could be pared from your table.

This new information also means that the chances of being overrun by temporary accounts are reduced enormously to the point that it's hardly worth considering as an issue (even if you felt it was before).

[ck9663]

Ideally, you can have another column on your Account table to identify which ones are (your) internal codes and which ones are user-defined. On your GUI, display only those that are internal codes and yet you still have the user-defined on your table. If you have (and you should) an referential integrity checks on your Transaction table with respect to the Acc_Code , you will be able to keep your data relationship and keep the data that you need.

Good Luck!!!

~~ CK

[alkos333]

I apologize for the lack of information. Maybe the following will clarify things a bit:

I have all possible accounts stored in the database. There around around 2000 of them. I have also set a bit to determine which ones to display to the user.

Obviously we don't want to overwhelm the donors with all these accounts. Hence, we display only the most common ones. However, we still want to give them an alternate way of specifying an account that's not displayed and that's why there is this text box in the UI which would allow them to do so. I'm just trying to figure out the best way to store these so-called "write-ins."

Hope this has clarified things a bit. Once again, my apologies for ambiguity.

[ck9663]

Thinking more of keeping the referential integrity of your tables, I'd still say you keep it in the same table.

Happy Coding!!!

~~ CK

[alkos333]

That makes sense. Currently Accounts table's primary key is a 6-character field. That's because this database is just a temporary storage and I'm using the types defined in our primary database. Therefore, for the "write-in" accounts, I can set this field to something like "user" and add another column which would be of type GUID and would serve as a primary key for the entire Accounts table. What do you think?

[NeoPa]

That's a good question.

I would consider making an effort to keep the current index field as unique, even if that becomes somewhat tricky (and it may well do so). There is a benefit to having that as a constraint for new data. That said, if you're absolutely certain all possible accounts are already entered, obviously at this stage all uniquely, then your suggestion may be appropriate for you. I don't want to steer you towards a solution that ends up limiting your choices down the line though.

One of the problems associated with keeping the current PK would be the fact that, even removing redundant records when the transaction has been 'properly' assigned, will still leave the process eating through the limited available list of usable codes. Obviously the PK field would need to be assigned automatically for the unofficial records, and this normally relies on adding one to the current maximum (one way or another). Thus the possibility for losing chunks of the available values and, in time, running out - even though by that time there would still be many gaps earlier in the sequence from subsequently deleted records. I think this could probably be got around, but it would at least be fiddly, if not downright complicated.

[alkos333]

Alright, quite a lengthy reply there :). So basically, you support my idea of using a GUID, correct?

[NeoPa]

Correct :)

Although with the proviso that you may find things somewhat less convenient than having the original PK as unique, which does give some benefits.

[alkos333]

How's having an original 6-char PK in this case is going to be more beneficial as supposed to a GUID?

[NeoPa]

It's not so much about having it as the PK per se, but rather as maintaining it as a unique index (which a PK would also be of course). Unique indices can be used in relationships; They can maintain data integrity for you more automatically; They can be more user-friendly if your users are used to dealing with these codes.

None of these is necessarily critical (hence my earlier comments), but personally I wouldn't lose them if I had the alternative. In your situation (as far as I understand it) it may well be a very practical option.

[alkos333]

Oh yeah, I agree with you. I'm planning on keeping them.