3

There is a table called Item(id, name, cost) to track Items possible and Orders(id, bill_id, item_id, units) which is created to track orders placed where same bill_id means it belongs to a single order.

How to impose an additional constraint in DB that says Item should be "Available" (at that point of time) if it needs be to added as item_id in the Order table?

My preferred schema design is to add a Type column which would have "Available" and "Unavailable" fields. But how can I check the Foreign Key constraint item_id should not only be a Primary Key in Item table, its Type should be Available as well.

Couple of answers using check constraints seems close How to make a foreign key with a constraint on the referenced table in PostgreSQL and Foreign key with additional relationship constraint.

Another schema design which I don't prefer is to have a Table called "Menu" which could have only the Items Available. The problem is that this table is going to very dynamic and transactional in nature and it keeps changing depending on the availability of items. And I am just creating a subset table out of Items depending on its state which doesn't seem to be a nice idea.

Which schema design and approach would you chose?

Use cases are like a retailer wants to make sure all his order are against items that are "Available". Ofcourse its possible to change "Available" to "Unavailable" at a later point of time. Orders can be made to maitain a constraint, but I am not sure if one track historically if it was really available or not from even this column. I don't if RDBMS can store such info.

Community
  • 1
  • 1
Nishant
  • 20,354
  • 18
  • 69
  • 101

1 Answers1

1

AFAIK, I do not think any RDBMS allows one to provide additional constraints along with referential integrity constraints. In your scenario, you should verify if an item is currently available by executing an SQL SELECT statement on Items table and if so add the item to Orders table in a single database transaction. Of course you can have Type or Status column in Items table to check if an item is available. Or you may want to have stock column in the table to check if an item of required quantity/units is available.

You can implement using your idea of having Type column and using check constraint but I think it is not a good idea. In this case you have to have Type column not only in Items table but also in the Orders table. Then have a check constraint on the Orders table as CHECK (Type = 'Available').

There is already a similar question PostgreSQL check constraint for foreign key condition

This is going to solve your problems by using user defined function.

Community
  • 1
  • 1
mvsagar
  • 1,998
  • 2
  • 17
  • 19
  • So what is the best idea for this problem from a theoretical perspective? Should all this be dealt at programming level and not DB level? DB just holds the data after such constraints have been applied either using DB constraints or programatically. – Nishant Mar 20 '16 at 12:21
  • Or there is this other schema design I just said which has all the avaliable items in a table called "Menu" whose primary key I can use. However it sounds like really redundant data. – Nishant Mar 20 '16 at 12:28
  • 1
    My suggestion is to deal at programming level and not at DB level. Do not use your "Menu" idea. – mvsagar Mar 20 '16 at 12:31
  • Thanks, yes I was thinking about this initially. Also appreciate letting me know that "Menu" idea is bad. You can see such horrible designs everywhere. Probably I can do it in "Model" which is supposed to have business level logics like this. – Nishant Mar 20 '16 at 12:34
  • 1
    But you can deal at DB level as well as is done in a solution provided for similar question: http://stackoverflow.com/questions/23237471/postgresql-check-constraint-for-foreign-key-condition. This looks like a perfect fit for your app, – mvsagar Mar 20 '16 at 12:39
  • Ok I really like keep things at DB level wherever possible. I don't have any app, its just theoretical question with a fake use case J :-) And yeah its better to put the logic in DB using user-defined function since thats more closer to the data. Also all consumers of the DB can have benefit. – Nishant Mar 20 '16 at 12:41
  • 1
    The only disadvantage with dealing at DB level in this particular case is the user defined function is not portable across RDBMs. Each RDBMS has its own way of defining functions and procedures! – mvsagar Mar 20 '16 at 12:54