I'm using the Milkman example to explain what I'm trying to achieve.

I have a main class, with a PK and a dozen of FKs. Let's call it a milkman. The FKs relate to the different "classes" that he's part of. For example, he is a person (ID details, gender, wtv), he has an employer (TaxId, Name, Location), he has a family (spouse, descendants), those descendants are persons.. You get the picture.

So I already have a scheme with a few tables with relations between them, all leading to my milkman table. So, the milkman, hence he's a milkman after all, will distribute its product through a said path. The path is composed by different establishments, they can be "houses" (1 type of class) or "companies" (another type of class) or ... - assume there are more possibilities. And houses and companies each have their ID (PK).

Each milkman has its path. So, Milkman1 will go to House1 first, then will go to House2, and then will finish his day in Company1, for example.

My C background asks me to do a linked list: each class will have a "Previous ID" and a "Next ID" field. And using that I can link stuff. But that stuff, isn't actually related to each other via FK. So if I draw a scheme of my database, both Houses and Companies are tables that have no relation with Milkman. They will not be attached to anything, really.

So, is there a way to go around this problem, creating relationships between the Milkman and his delivery locations?

If not, would it be possible to do it if we only had Houses and not Companies (only 1 class)?

I don't know if I'm missing something (my poor SQL knowledge hasn't been tested in a while), or if I'm too focused on creating relations PK-FK and there's no need for it, and I can work things out in another way (for example for DELETE CASCADE) on the C# programming layer.

I'm using the Milkman example to explain what I'm trying to achieve.

I have a main class, with a PK and a dozen of FKs. Let's call it a milkman. The FKs relate to the different "classes" that he's part of. For example, he is a person (ID details, gender, wtv), he has an employer (TaxId, Name, Location), he has a family (spouse, descendants), those descendants are persons.. You get the picture.

So I already have a scheme with a few tables with relations between them, all leading to my milkman table. So, the milkman, hence he's a milkman after all, will distribute its product through a said path. The path is composed by different establishments, they can be "houses" (1 type of class) or "companies" (another type of class) or ... - assume there are more possibilities. And houses and companies each have their ID (PK).

Each milkman has its path. So, Milkman1 will go to House1 first, then will go to House2, and then will finish his day in Company1, for example.

My C background asks me to do a linked list: each class will have a "Previous ID" and a "Next ID" field. And using that I can link stuff. But that stuff, isn't actually related to each other via FK. So if I draw a scheme of my database, both Houses and Companies are tables that have no relation with Milkman. They will not be attached to anything, really.

So, is there a way to go around this problem, creating relationships between the Milkman and his delivery locations?

If not, would it be possible to do it if we only had Houses and not Companies (only 1 class)?

I don't know if I'm missing something (my poor SQL knowledge hasn't been tested in a while), or if I'm too focused on creating relations PK-FK and there's no need for it, and I can work things out in another way (for example for DELETE CASCADE) on the C# programming layer.

• sql

• 1 I would use a delivery table that has fkMilkman, fkEtablishment, sequence, and a DateTime stamp. – Bart McEndree Commented Jan 22 at 14:39
• @BartMcEndree I assume you're suggesting adding a big enough table to fit the largest possible path, with as many columns for establishments as needed. But, fkEstablishment is an FK, so.. it cannot be nullable. Would you have a default establishmentID in all of them? and replace it with the respective establishmentID when you go through it? – ATSlooking4things Commented Jan 22 at 14:42
• 1 No. each row is a stop. Delivery table has pkDelivery and a DeliveryGroup. Milk man might be assigned a delivery group in the morning and another delivery group in the afternoon. So 5 tables (Staff, Delivery, Establishment, EstablishmentType, DeliveryGroup). – Bart McEndree Commented Jan 22 at 14:46
• @BartMcEndree I'm not sure I understand it. DeliveryGroup is where the sequence is? Staff has a Delivery, and follows a DeliveryGroup, through Establishments and each has its EstablishmentType? But what exactly is DeliveryGroup? What would be the columns? And, are the sequences pre-defined or will be added when they occur? What if I have 300 possible sequences? Wouldn't it be hard (computationally costly) to keep checking if the current sequence exists, and if not, to add it? – ATSlooking4things Commented Jan 22 at 15:28
• 1 I made a fiddle that shows an example of the tables I suggested dbfiddle.uk/v3dANOdd – Bart McEndree Commented Jan 22 at 17:01

1 Answer 1

Here is a table structure you could use

https://dbfiddle.uk/70p8XwV_

Create Table EstablishmentType (
  pkEstablishmentType integer IDENTITY(1,1),
  TypeName varchar(512),
  created_at datetime DEFAULT (getdate()) 
);

INSERT INTO EstablishmentType (TypeName) VALUES 
  ('Residential'),
  ('Business'),
  ('Orphanage'),
  ('Hospital'),
  ('School'),
  ('Special');


SELECT * FROM EstablishmentType

Create Table DeliveryGroup(
  pkDeliveryGroup integer IDENTITY(1,1),
  GroupName varchar(512),
  Duration integer,
  Milage integer,
  FuelCost integer,
);

INSERT INTO EstablishmentType (TypeName) VALUES
('Residential Morning route'),
 ( 'Residential Afternoon route'),
 ( 'Business Morning route'),
 ( 'Business Afternoon route'),  
 ( 'Special Deliveries');

SELECT * FROM EstablishmentType

Create Table Staff (
  pkStaff integer IDENTITY(1,1), 
  Firstname varchar(512),
  Lastname varchar(512),
  Job varchar(512),
  DOB varchar(20),
  Sex varchar(20),
  Supervisor integer,
  created_at datetime DEFAULT (getdate()) 
);

INSERT INTO Staff (Firstname,Lastname,Job,DOB,Sex,supervisor) VALUES
('Amanda','Jones','Supervisor','1990-1-1','F',-1),
('John','Smith','DeliveryDriver','2000-1-1','M',1);

SELECT * FROM Staff


Create Table Establishment(
  pkEstablishment integer IDENTITY(1,1),
  fkEstablishmentType integer,
  Name varchar(512),
  Address varchar(512),
  created_at datetime DEFAULT (getdate()) 
);

INSERT INTO Establishment (fkEstablishmentType,Name,Address) VALUES
(1,'Mr Adams','123 South ave'),
(1,'Mr Carie','124 South ave'),
(1,'Ms Walls','126 South ave'),   
(2,'Express Resto','127 South ave'),   
(2,'Subway','128 South ave');     

SELECT * FROM Establishment

Create Table Delivery (
  pkDelivery integer IDENTITY(1,1), 
  fkDeliveryGroup integer,
  fkStaff integer,
  fkEstablishment integer,
  Sequence integer,
  comment varchar(512),  
  deliveryScheduled_at datetime,  
  deliveryCompleted_at datetime  
);

INSERT INTO Delivery (fkDeliveryGroup,fkStaff,fkEstablishment,Sequence) VALUES
  (1,1,1,1),
  (1,1,2,2),
  (1,1,3,3),
  (2,1,4,1),
  (2,1,5,2);

SELECT * FROM Delivery

SELECT *
FROM Delivery D
LEFT JOIN Establishment E ON E.pkEstablishment=D.fkEstablishment
LEFT JOIN EstablishmentType ET ON ET.pkEstablishmentType=E.fkEstablishmentType
LEFT JOIN Staff S ON S.pkStaff=D.fkStaff
LEFT JOIN DeliveryGroup DG on DG.pkDeliveryGroup=D.fkDeliveryGroup