Design doubt

fcoexposito · #1 (**permalink**) 07-16-09, 07:50

Hello,

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.

How can I solve this?

Thanks in advance

andrewst · #2 (**permalink**) 07-16-09, 09:20

It is difficult to enforce this rule in the data model you have, except through procedural code or database triggers. A possible alternative is to redesign your tables to be like this:

Code:

create table offer 
( offer_shop_id references shop
, offer_id ...
, primary key (shop_id, offer_id)
);

create table offer_product
( offer_shop_id ...
, offer_id ...
, product_shop_id ...
, product_id ...
, foreign key (offer_shop_id, offer_id) references offer
, foreign key (product_shop_id, product_id) references product_shop
, check (offer_shop_id = product_shop_id)
);

This redundantly stores the shop_id twice in offer_product, so that the check constraint can enforce the rule.

beilstwh · #3 (**permalink**) 07-16-09, 09:33

I have another way of doing this. Use a many to many table

Code:

create table product 
( product_id number(15) primary key,
.....
);

create table shop
( shop_id  number(15) primary key,
 .....
);

create table available
( product_id number(15) not null,
  shop_id    number(15) not null,
  number_available number(10) not null,
 foreign key (product_id) references product,
 foreign key (shop_id) references shop,
 primary key (product_id,shop_id));

Then have your offer table check the available table to see if the shop sells the product and if product is available.

fcoexposito · #4 (**permalink**) 07-16-09, 09:53

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?

Thanks

andrewst · #5 (**permalink**) 07-16-09, 11:06

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.

chuck_forbes · #6 (**permalink**) 07-16-09, 11:43

Hibernate is just a Data Access Layer. So, it should be able to handle it. We're just getting into it, and encountering what to do when multiple tables are involved.

The easy way to solve it would be to create VIEW, with an instead of trigger, and connect hibernate to that, but, that's not in the spirit of Hibernate, I guess

fcoexposito · #7 (**permalink**) 07-16-09, 15:14

As I've read https://www.hibernate.org/116.html#A28, hibernate supports views, except that you might not be able to update or insert to a view.

Then I have to reject that option... Now I have two options:

1) My first option and control the possible errors by code.
2) andrewst option.

What do you think is the better option?

Thanks in advance.

chuck_forbes · #8 (**permalink**) 07-16-09, 15:22

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.

--=Chuck

fcoexposito · #9 (**permalink**) 07-17-09, 04:49

Just for info, I've tried what chuck_forbes wrote in last mail and it works with Hibernate.

I'll try to check if it works with more than one related tables.

Thanks to all.

JRowbottom · #10 (**permalink**) 07-17-09, 09:18

Would this design work:

Code:

CREATE TABLE PRODUCT 
(product_id.....(primary key))

CREATE TABLE SHOP
(shop_id.....(primary key))

CREATE TABLE PROD_SHOP_LINK
(prod_shop_id .....(primary key)
,product_id          (foreign key)
,shop_id              (foreign key))

CREATE TABLE OFFER
(offer_id         (primary key)
,shop_id         (foreign key))

CREATE TABLE OFFER_PROD_SHOP_LINK
(o_p_s_id             (primary key)
,prod_shop_id       (foreign key)
,offer_id              (foreign key))

The Offer_Prod_Shop_Link table would record what products for a shop were in each offer for that shop.

chuck_forbes · #11 (**permalink**) 07-17-09, 11:08

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:

Code:

create table employee
(employee_id number primary key,
 name varchar2);

create table city
(city varchar2 primary key,
 state varchar2);

create table works_in
(employee_id varchar2,
 city varchar2);

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.

--=Chuck

Internet Services

Web Development

Digital Marketing

Consumer Tech