CODEWITHSUNDEEP
sqldatabase-design
Jezen Thomas
Jezen Thomas

Reputation: 13800

Improving a DB design where a prefix designates a relation

I’m currently rewriting an old accounting system, and I’ve found a database design that I suspect is quite poor. I don’t have particularly much experience with database design, so I can’t be sure.

In the old system there is a Transactions table, and every row in this table has a foreign key to another table which is determined by some prefix. For example:

| ID | VOUCHER  |
|  1 | L-100801 |
|  2 | U-120407 |
|  3 | Z-622909 |

There are more than two columns in this table (about 15 actually). If a row’s voucher begins with L, it’s connected to the Client Invoices table. If it begins with a U, it’s connected to the Payments table, etc.

Am I right in thinking this is a poor design? How can I improve on it? Should each type be separated into its own column? Should there be a table for Voucher types which I use for connecting records through?

Upvotes: 1

Views: 71

Answers (2)

Ram
Ram

Reputation: 3091

Am I right in thinking this is a poor design?

Yes, that is very poor database design. I have had a seen similar design at a company I have worked before. It makes it very difficult to query the database based on this table.

Should each type be separated into its own column?

It depends on the entries in the tables, like if almost every id is connected to Client Invoice, Payments and other tables then it is good and less complex approach. Based on your sample data this is not right approach.

Should there be a table for Voucher types which I use for connecting records through?

This is a better way to go ahead with as it is similar to tackling a many to many association by creating an association table to represent the association. This was the way the company I worked at redesigned their database.

Upvotes: 3

user2338816
user2338816

Reputation: 2163

There are principles that can be used to design relational databases. In the case of a value such as L-100801, the logical problem for relationships is that the single, combined value encodes two separate meanings that are generally un-related to each other.

If the two sub-values are stored in separate columns, they can be easily grouped together to allow the set-at-a-time processing that SQL is designed to handle. They can be queried separately, ordered separately, etc.

If needed, permissions might even be granted separately to allow appropriate users to work only with data that relates to their job responsibilities.

Upvotes: 1

Related Questions