I have a design doubt. I hope the attachment file helps.. I have a shop which can have n products and a product can belong to n shops. This is a n:n relation. Then every shop can have n offers. This is a 1:n relation. And then my doubt... Every offer can have n products and every product can be in n offers. This is a n:n relation. But the problem is: in my design I can add to an offer of a shop a product which not belongs to this shop.
I don't understand what the second one can solve my issue. Could you try to explain to me?
About the first answer, I think it is really strange (for me) but it seems to be ok.. Do you know any other option? I don't have any problem to drop and create tables... I am at the beginning of my project. Also, do you know if that solution could be treated with Hibernate?
I only know of one other potential declarative solution - i.e. one defined via the data model and constraints rather than via procedural code. That involves creating a materialized view that selects offer_products where no corresponding shop_product exists, and adding a check constraint to the MV that always fails! Details in an old blog post of mine.
To be honest, that is probably overkill, and I have never done it on a real project myself. In my projects, the database is always updated via PL/SQL APIs that enforce such rules procedurally.
I know nothing of Hibernate, so can't comment on that I'm afraid.
It might just be referring to the fact that for some rdbms's, VIEWS are not updatable. In Oracle, they are. That issue sits on top of Andrewst's solution, btw (i.e. once you've stored your business rules in your model's design, how do you get the data in & out).
As far as Hibernate goes, there's no way it could tell a VIEW apart from a TABLE. Just try it. Create a table. Then create a view (as select * from table) - in this case you won't need an Instead Of TRIGGER. Then grant INS/UPD/DEL rights against the VIEW, and connect Hibernate to the view, & try it out.
Using DML against a VIEW with mutliple tables works, but there are things you have to take into account. If you're SQL pulls out data where there is a key-preserved table, and that's the table you're changing, then you don't need an INSTEAD OF TRIGGER.
However, if you are trying to update a non key-preserved table, then you'll need that TRIGGER on the VIEW.
The best I can do to define "key-preserved"? If you have a view with 2 tables, and they're related via a many-to-one relationship, then the table on the "many" side is the key-preserved table (there is a unique primary key displayed on every record when they VIEW is executed).
However, if you have a many-to-many relationship implemented via a cross-reference table, like where employees are assigned to multiple cities:
create table employee
(employee_id number primary key,
create table city
(city varchar2 primary key,
create table works_in
Then the only table you can update in the VIEW without an INSTEAD OF TRIGGER is "works_in". To update the other tables, you'd need the IT TRIGGER, since neither of their primary keys are guaranteed to be unique when the data from the VIEW is generated.