Insert Record based on flag

Question

I have a table say tb_load_files. It contains fields file_name, file_date,file_loc,file_status

Now, File_status cane be x or y. The requirement is there can be multiple records for x status for combination of (file_name, file_date,file_loc) but only one record for y status.

e.g.

file_name  file_date     file_loc  status
abc.txt     12-oct-07     NY         X
abc.txt     12-oct-07     NY         X
abc.txt     12-oct-07     NY         Y

abc.txt     12-oct-07     NY         Y    --  NOT ALLOWED

what could be the best way of designing these table?
  a. selecting data before insert
  b. trigger to check if value exists
or any other. Please advice

Answer 1

If there is going to be thousands of entries, I'd suggest that instead of wasting space by using multiple rows with the same data, add a column COUNT. The data can be then unique and can be better optimized with indexes if necessary.

Then, either create PL/SQL procedure callable by client, or (if client expects to work with table) create a updatable view + INSTEAD OF INSERT/UPDATE/DELETE triggers. Code inside it will just increase COUNT on existing record when status is X, or throw error if status is Y.