Database Model Normalization - Data Integrity

Question

Here is my question, I have 2 basic entities.

1-Order and 2-Delivery Type (basically a look up table 1-email, 2-download, 3-USPS Mail)

Let's say a customer places an order and chooses delivery type 3-USPS Mail.

My orders table stores the id 3 to join to the Delivery Type. Basic stuff.

but what happens when an admin by mistake updates the delivery type from UPS mail to Fedex Mail.

The order will reflect the wrong delivery type. My question is, should we prevent people from updating the delivery type once an order has been places or my order should be denormalized and store a snapshot of the document type at that moment what's the best practice for this?

I see that most of the time for example in orders-products scenarios, updating a product name is not a big deal but whats the procedure to implement when the new value impacts the order?

Thanks!

score 0 · Accepted Answer · answered Mar 06 '15 at 23:43

0

That is going to depend entirely on the business process you're automating there. If they should not be able to do, and there is no business reason to do it, then they should be prevented from doing it. If they need to do it, then the system needs to be able to handle it. It's the kind of thing you need to model from the start, to keep from getting into holes you can't get out of.

So, short of it is, there's no firm answer. Your data needs to match what is currently going on with the order, that's for certain.

answered Mar 06 '15 at 23:43

CargoMeister

4,199
6
27
44

It makes sense, yes, our business rule is clear: if they try to change any values after an order has been place it means 2 things 1) they are trying to cheat 2) it is an honest mistake and they are trying to make corrections. the 2nd scenario is no common, it is like trying to change the name of iphone after they release it to the market. Thanks for your quick reply. – user714142 Mar 07 '15 at 00:01
No problem. If they shouldn't do something, make it impossible to do it. Because if they can, they will. :D (A few old scars from that kind of thing....) – CargoMeister Mar 07 '15 at 00:09

score 0 · Answer 2 · answered Mar 07 '15 at 00:08

but what happens when an admin by mistake updates the delivery type from UPS mail to Fedex Mail.

Don't let that happen. Privileges are part of database design, illustrated below using PostgreSQL. Principles apply to all client/server SQL dbms.

create table delivery_types (
  delivery_type_num integer not null primary key,
  delivery_type_name varchar(15) not null unique
);

insert into delivery_types values
(1, 'Email'), (2, 'Download'), (3, 'USPS Mail');

create table orders (
  order_num integer primary key,
  other_columns char(1) not null default 'x',
  delivery_type_num integer not null references delivery_types
    on delete no action on update no action
);

revoke insert, update, delete, truncate on delivery_types from public;
grant select on delivery_types to public;

By revoking those privileges from the role "public", only the owner and database superusers can update that table. The foreign key references in "orders" will prevent the owner and database superusers from deleting rows or updating id numbers of rows that are referenced by other tables.

A slightly different approach uses natural keys. This is not denormalization. Both tables "delivery_types" are in 6NF.

create table delivery_types (
  delivery_type varchar(15) primary key
);

insert into delivery_types values
('Email'), ('Download'), ('USPS Mail');

create table orders (
  order_num integer primary key,
  other_columns char(1) not null default 'x',
  delivery_type varchar(15) not null references delivery_types
    on delete no action on update no action
);

revoke insert, update, delete, truncate on delivery_types from public;
grant select on delivery_types to public;

insert into orders values 
(1, 'x', 'Email'), (2, 'x', 'Download'), (3, 'x', 'USPS Mail');

The privileges work just as before--members of the role "public" can select from delivery_types, but they can't change the content. But now even the database superuser can't delete or update rows in delivery_types. The foreign key references prevent it.

Database Model Normalization - Data Integrity

2 Answers2