CODEWITHSUNDEEP
c#sqlsql-serverentity-framework
Boggot
Boggot

Reputation: 79

Database Design Circular Reference

I am trying to modify a C# .NET MVC web app and existing database to implement some new functionality. However, try as i might, i cannot modify the database structure without creating a circular reference and i was hoping someone may have a solution.

I want to implement a method of order routing. So depending on thresholds set on a customer an order will be produced one of two ways depending on the order size. So i find when i design the database the customer needs to reference the routing table as it dictates when an order comes where it should go. Also because an order is attached to a route it also needs to reference the routing table which results in a circular reference:

enter image description here

The customers, products and order are already in production so i would be nervous about modifying those relationships if required.

Any help is greatly appreciated! Thanks in advance.

Upvotes: 0

Views: 339

Answers (2)

BA.
BA.

Reputation: 934

By the way, you don't have a circular reference! If I understand the problem, you want to configure each customer with one of two routes (above and below threshold). Then, the order will go as per that route (order route)

First, the meaning of the foiregn key in the above three relationship is different, one determining preference, and one determining actual

Then, the actual route that the order took is the one attached to it. If you don't have a requirement of tracking history (like, how does this order took this route while the customer is constraint to one of different two?) then I see your design - for this particular question - is valid

I still don't know why the PRODUCT table is linked to customer the way it is! Maybe it is then a CUSTOMER_PRODUCT, but that's outside your question

You can get the route certain order needs to take by selecting:

 select case when :thresh > threshold then aboveThresholdRoute else belowThresholdRoute end
 from customer 
 where id = :customer_id

Where threshold is calculated based on whatever formula you have, and for the order customer

Upvotes: 1

zaitsman
zaitsman

Reputation: 9509

First of all, the schema that is already in production does not seem extensible enough. However, your customer does NOT need to be referencing Route. A Route is a property of an Order, and an Order is a property of the Customer (via Product association).

So you need to create a new Routes table, like so

CREATE TABLE Routes
(Id UNIQUEIDENTIFIER,
Name NVARCHAR(50));

Then alter Order table like so:

ALTER TABLE Order ADD RouteId UNIQUEIDENTIFIER;
ALTER TABLE Order ADD CONSTRAINT FK_Routes_Id FOREIGN KEY (RouteId) REFERENCES Routes(Id);

Then, to select the Routes for the user:

SELECT * FROM Routes 
INNER JOIN Order 
ON 
    Routes.Id = Order.RouteId
INNER JOIN Product
ON
    Product.LastOrder = Order.Id -- Assuming that is the FK here, not clear from the question
INNER JOIN Customers
ON 
    Product.CustomerId = Customers.Id
WHERE 
    Customers.Id = 'some value here'

UPDATE Since you now mentioned routes are user specific, rather than order specific, you can simply change it like so:

CREATE TABLE Routes
(Id UNIQUEIDENTIFIER,
Name NVARCHAR(50));

Then alter Customers table like so:

ALTER TABLE Customers ADD RouteId UNIQUEIDENTIFIER;
ALTER TABLE Customers ADD CONSTRAINT FK_Routes_Id FOREIGN KEY (RouteId) REFERENCES Routes(Id);

Then to select route for an order, do:

 SELECT * FROM Routes 
    INNER JOIN Customers 
    ON 
        Routes.Id = Customers.RouteId
    INNER JOIN Product
    ON
        Product.CustomerId = Customers.Id
    INNER JOIN Order
    ON 
        Product.LastOrder = Order.Id -- Assuming that is the FK here, not clear from the question
    WHERE 
        Order.Id = 'some value here'

Upvotes: 0

Related Questions