CODEWITHSUNDEEP
sqlsql-server
user10471965
user10471965

Reputation:

Implementation of a Recursive Relationship

So, I'm currently working with mssql server manager and I encountered a problem.

I need to implement a recursive relationship and I don't seem to get it to work in such a way that it doesn't have redundancy.

The problem occurs on the following table: I got a table: 'Order', An order can have multiple 'Suborder'

Now, I have no idea how to implement this in such a way in mssql that it doesn't have redundancy.

Edit: Redundancy means that the database saves data multiple times.

In this case that would mean. The main order would be saved multiple times into the database because there are multiple suborders attached to it.

Example:

INSERT INTO order (order_id, order_name, suborder) VALUES (1, Intel, Network);
INSERT INTO order (order_id, order_name, suborder) VALUES (1, Intel, Finance);
INSERT INTO order (order_id, order_name, suborder) VALUES (1, Intel, Datamodel);
INSERT INTO order (order_id, order_name, suborder) VALUES (2, AMD, null);
INSERT INTO order (order_id, order_name, suborder) VALUES (3, NMI, null);

In this way, it saves order_id and order_name multiple times, and that's just what not should happen.

Upvotes: 0

Views: 502

Answers (1)

GuidoG
GuidoG

Reputation: 12059

You need much more tables for master child relation
Look up the term Normalization and read more about it

for example

 table Client (ClientID, Name, Addres, ...)

 table Article (ArticleID, Name, Stock, ...)

 table Order (OrderID, ClientID, DeliveryAddress, ...)

 table SubOrder (SubOrderID, OrderID, ArticleID, Quantity, ...)

In this design the order data is only saved once, and each SubOrder only saves the OrderID to maintain its relation to order.
Also data about the client and Article is only save once, only ID's are save multiple times

I have used key names like ClientID and ArticleID in this example, you will find that often the name ID is used instead. This was just to make the relation in the example more clear

Upvotes: 1

Related Questions