Any databse admins around?

[drak3]

Hello,

I want to set a constrain in my database, however due to the situation I find it difficult.

I have a M:N relationship between customer and order and would like to constrain the maximum number of orders of the customer to 5.

This is obviously not a matter to add a check under the customer or order table creation since everything is going on in the junction table.

I appreciate your thoughts.

Thanks.

PS: DBMS is postgreSQL

 

[rpstewart]

Not a postgres expert but I'd suggest that sort of constraint is probably better handled by the application that's updating the database rather than trying to get the database to do it.

 

[Visage]

Not used postgre (im an Oracle bod), but what I would do is have a mapping table from order_id to customer_id, and have a BEFORE trigger that essentially says (if count(*) from mapping_table where cust_no = <whatever> = 5 then reject the insert)

 

[JIMA]

Hi,

Again, I'm not a postgreSQL user, but in Ingres this could be done by using a database rule + procedure.

However, I'd take rpstewart's view that this should form part of the business model's logic in the application. Putting this kind of logic on the database makes it hard for someone looking at the code to know what's going on. Also, it's easy to accidentally remove such a rule without knowing it e.g drop the table that the rule is on in the case of Ingres.

You've also got the issue that you've allowed the user to do something (put an order together) and then spent time accessing the database only to tell them they can't do what they want. You can save the user a lot of time if you put the code in the business model so a check can be done to ensure that the user can submit another order before allowing them to compile the order. This code can then be reused to recheck the situation just before you commit the order in the save (so they can't submit more than the max orders by logging on multiple times).

Jim