Thanks for the question, Geraldo.
Asked: October 29, 2019 - 3:33 pm UTC
Last updated: November 05, 2019 - 3:59 am UTC
Version: 18.4
Viewed 1000+ times
You Asked
Hello, Ask Tom Team.
I have a table A with columns (client_id,invoice_number,invoice_type) with a composite primary key (client_id,invoice_number). At business level there are 5 invoice_types (70,71,72,73,74). The invoice_number always brings the invoice_type taking position 2 an 3 of string. A client_id can have the same invoice_number as another client_id.
Data Example:
Table: A
client_id invoice_number invoice_type
123456 Z7000001 70
654321 Z7000001 70
123456 Z7100001 71
123456 Z7400001 74
Requirement:
Now, the business introduces a new invoice_type (75). This invoice_type can be repeated many times as client sends the data. There is no restriction here (primary key is broken here).
Table: A
client_id invoice_number invoice_type
123456 Z7000001 70
123456 Z7000003 70
654321 Z7000001 70
987654 Z7100001 71
123456 Z7400004 74
987654 Z7500001 75
987654 Z7100001 75
How can we accomplish this in the best possible way knowing that invoice_type (70,71,72,73,74) can not be repeated in the table?
What would be the suggested db design?
Thanks in advanced.
and Connor said...
In terms of a true *design* change, you would end up with another table to potentially track the multiple records for invoice type 75.
But in terms of a quick fix, as long as you have something that defines uniqueness for invoice type 75 records (lets say it was an transaction date/time), then you could adjust your uniqueness definition to be:
create unique index IX on tableA
(client,invoice_number,
case
when invoice_type in (70,71,72,73,74) then invoice_type
when invoice_type = 75 then transaction_date
end
)
This could be done via the index as above, or you could add that case expression as a virtual column and define a standard uniqueness constraint using that.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment