0

We have a situation in a database design in our company. We are trying to figure out the best way to design the database to store transactional data. I need expert’s advice on the best relational design to achieve it. Problem: We have different kind of “Entities” in our system, for example; Customers, Services, Dealers etc. These Entities are doing transfer of funds between each other. We need to store the history of the transfers in database.

Solutions:

  1. One table of transfers and another table to keep “Accounts” information. There are three tables “Customers”, “Services”, “Dealers”. There is another table “Accounts”. An account can be related to any of the “Entities” mentioned above; it means (and that’s the requirement) that logically there should be a one-to-one relationship to/from Entities and Accounts. However, we can only store the Account_ID in the Entities table, but we cannot store the foreign key of Entities in Accounts table. Here the problem happens in terms of database design. Because if there is a customer’s account, it is not restricted by the database design to not be stored in Services table etc. Now we can keep all transfers in one table only since Accounts are unified among all the entities.
  2. Keep the balance information in the table primary Entities table and separate tables for all transfers. Here for all kind of transfers between the entities, we are keeping separate tables. For example, a transfer between a Customer and Service provider will be stored in a table called “Spending”. Another table will have transfer data for transfer between Service and Dealers called “Commission” etc. In this case, we are not storing all the transfers of the funds in a single table, but the foreign keys are properly defined since the tables “Spending” and “Commission” are only between two specific entities.

According to the best practices, which one of the above given solutions is correct, and why?

DJ'
  • 1,760
  • 1
  • 13
  • 26
  • Your question concerning best practices has to be informed by several centuries worth of practices regarding double entry bookkeeping. To some extent, the digital revolution has made some of these practices obsolete. But many of them remain good practices, perhaps even "best practices". Why is a general ledger system not part of the solution for your enterprise, even if you have to roll your own in order to integrate it with your other data requirements? – Walter Mitty Aug 14 '16 at 13:03
  • When I asked about the best practices, I meant best practices according to database schema design, not accounting. For now the problem is with the database schema, general ledger system will be built on top of the accounts I mentioned. Off-course I cannot explain the complete database in one question. – DJ' Aug 15 '16 at 08:33
  • I get it that it's about a schema. My comment was intended to point you to published schemas in the database accounting world that accomplish precisely what you are trying to do, track transactions involving customers, dealers, and services. – Walter Mitty Aug 15 '16 at 11:12
  • Great! Now please advice on how to achieve it when we have three different entities, and can you give me some links on the published schema? – DJ' Aug 15 '16 at 12:00

2 Answers2

1

If you are simply looking for schemas that claim to deal with cases like yours, there is a website with hundreds of published schemas. Some of these pertain to storing transaction data concerning customers and suppliers. You can take one of these and adapt it.

http://www.databaseanswers.org/data_models/

If your question is about how to relate accounts to business contacts, read on.

Customers, Services, and Dealers are all sub classes of some super class that I'll call Contacts. There are two well known design patterns for modeling sub classes in database tables. And there is a technique called Shared primary Key that can be used with one of them to good advantage.

Take a look at the info and the questions grouped under these three tags:

If you use class table inheritance and shared primary key, you will end up with four tables pertaining to contacts: Contacts, Customers, Dealers, and Services. Every entry in Contacts will have a corresponding entry in one of the three subclass tables.

An FK in the accounts table, let's call it Accounts.ContactID will not only reference a row in Contacts, but also a row in whichever of Customers, Dealers, Services pertains to the case at hand.

This may work outwell for you. Alternatively, single table table inheritance works out well in some of the simpler cases. It depends on details about your data and your intended use of it.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
0

You can make table Accounts with three fields with FK to Customers,Dealers and Services and it's will close problem. But also you can make three table for each type of entity with accounting data. You have the deal with multi-system case in system design. Each system solve the task. But for deсision you need make pros and con analyses about algorithm complexity, performance and other system requirements. For example one table will be more simple to code, but three table give more performance of sql database.

Artemy Prototyping
  • 170
  • 1
  • 9
  • Three columns will not solve the problem, as in this design you are technically allowing the schema to link one account to three different entities. – DJ' Aug 15 '16 at 08:31
  • You can keep it by business logic, when create one to one entity in Account table. But if you want more control over data consistency you can put constraints in rdbms. In triggers for example. – Artemy Prototyping Aug 15 '16 at 10:03
  • Complete analysis needed for advantages and disadvantages for each data model design decision. In this case with three fields main disadvantage null values. It's not good. Model with three tables more clear and sql will be have more performance but code will be more complex and in future you can have problems with system development because you have logical entity "contactor" in you system, but it splited to three types. Each type have own model of calculation. At all i think you need to think about you business domain model first. – Artemy Prototyping Aug 15 '16 at 11:01
  • If you will be have clear bussines domain model you will get clear data model on next step. p.s. in my terms you must to see "beauty" in model. – Artemy Prototyping Aug 15 '16 at 11:03
  • We thought about it, but unfortunately it is not a good database schema design. – DJ' Aug 15 '16 at 11:13
  • what is you concern about it? – Artemy Prototyping Aug 15 '16 at 11:21
  • I want the schema itself to be self-sufficient in order to keep the data integrity. I do not want to rely on the data model / code to ensure data integrity. – DJ' Aug 16 '16 at 13:50
  • It's impossible in complex applications on RDBMS level. Really you can through triggers and stored procedures but more effective way use API and transaction for interaction with RDBMS. It's will give you more portability for you app. – Artemy Prototyping Aug 16 '16 at 18:17
  • We do not need to write trigger or do it on the programming level when it can be achieved on the database level to ensure data-integrity. This link helped us: http://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database look for Class Table Inheritance (aka Table Per Type Inheritance) – DJ' Aug 17 '16 at 06:48
  • Agree, it's good solution for (account)-to-(account type) relation. – Artemy Prototyping Aug 17 '16 at 10:32