May 31, 2002 at 8:56 am
I'm very new to database design and I've been given the job of redesigning an existing database and then doing the data migration. The question I have is there anywhere with examples of how certain processes are modeled? Example in the old system they have a payments table each row holds information about every payment type possible ie check, credit card, cash as well as credit card information and the batch processing results of any credit card transactions. It seems to me that every payment type need to be in a seperate table linked back to the main payment record as well as the batch results from the Credit card process. Is there anywhere on the web with examples of how some of these common processes are modeled?
thank for any help
Randy
May 31, 2002 at 9:15 am
Not that I know of. We have started something here (in alpha) that will allow people to post models and others to comment on them. We hope to build up a repository with some good design information people can use for themselves.
I'm not sure I understand your question, or am sure that it should be this way. In payment systems for orders, I have usually stored the common information in the same table. Then I have stored disparate informaiton in a child table, though I would be wary of using different types of tables. Will make queries very ugly.
If you post more informaiton about this particualar issue, I'm sure we can give you some ideas.
Steve Jones
May 31, 2002 at 10:54 am
Thanks for the reply, this has to be one of the best boards I've seen.
Here is the problem. I have a payments table
that holds all the payment information for each customer order. It currently has 47 columns which hold Payment, Credit Card information,Credit card authorization information, payment Status, checking account information, ( for echecks ) and Cod information. Currently the business accepts payment with credit card, check, cash, cod and echeck and all the information for each payment type is put into the same table. I guess my question is should this table be normalized, and how should the specific payment type information be handled. Credit cards have all the credit card information, authorization date and processing information. Cod has payment, Cod statement number was the Cod refused. Checks the check number and Echeck the address, bank routing information and checking account number. Seems to me there should be a standard Database pattern to handle payments and payment types. Any thought would be great.
Cheers
Randy
May 31, 2002 at 11:07 am
Normally you would aim to normalize, the big factor though is how many transactions take place over a given time and what would be the effect of normalizing. If it will slow down transactions to the point not as much is getting done then you wouldn't want to normalize (except maybe in a history set if you have that). I do the normalized thing with an inventory system here. The database has a common inventory table, things like serial location, person, etc. But item specific things like a computer or printer would have then they get their own tables. I have very little insert and update traffic so is not an issue for me. I use stored procedures with the business logic built in to decide based on the equipment type how to process and pull back together. Then on the web pages I have logic to make look like one unit of data.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 31, 2002 at 6:08 pm
I would look to put the common information (name, auth code, etc.) in the main table. For other info, you have three choices.
1. Use a single table and a "type" lookup (address, check no).
2. Use separate tables
3. Leave it as is.
Antares has some good info, though without seeing all the data you need and the places where it applies, it's hard to give you an answer.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply