Need help in table design

Question

Need help in table design

dsh_roy

Old Hand

Points: 335
More actions
March 14, 2014 at 8:46 am

#289420

Hi All,
Can someone give me the sample table design of Overpayments associate with transactions?
Thanks
Disha

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

That is not enough information to go off of. Please give more details for the needs of the table. There isn't a one size fits all table design, so the more information the better the answer will be. Also submit what you already have.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

dsh_roy (3/14/2014)
Hi All,
Can someone give me the sample table design of Overpayments associate with transactions?
Thanks
Disha

We need a lot more information before we can give you an example.

- What information is available and/or does your business need about the Overpayments and the transactions.

- What are the business rules that apply to these data?

- What type of value (string, numeric, boolean, etc.) does this information contain?

- How are the Overpayments and transactions are related to each other?

- Is there a relation with other (existing or new) data?

- ...

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Seems to me that overpayments is not its own table. I would have a payments table. Then you can easily determine overpayments by calculating the balance.

In other words, you would have a "Debts" or whatever table and a payments table. I would probably add a bridge table in there so that a given payment can be applied to more than one debt. It is then super easy to determine overpayments because the sum of payments applied to a given debt is more than the debt.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

To be honest, you shouldn't need an "OverPayments" table. My recommendation would be to hire a local pro to design your database(s) and the entities therein. If it was a local pro that decided to build such a table, it's time to find a new one. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)