0

Scenario

I need to design a equipment movement from many sources and destinations. I have the folowing sample tables:

CREATE TABLE Area{
    Id INT,
    Name VARCHAR(50),
    //some other fields
}

CREATE TABLE Stowage{
    Id Int,
    Name VARCHAR(50),
    //some other fields
}

CREATE TABLE Movement{
    OriginId INT,
    DestinationId INT,
}

But I need some kind of movement like:

  1. Origin : Area; Destination: Area
  2. Origin : Area; Destination: Stowage
  3. Origin : Stowage; Destination: Area
  4. Origin : Stowage; Destination: Stowage

But I only have two columns and needs more than one foreign key per column.

Posible solution in mind

  1. Create MovementArea, MovementStowage, MovementStowageArea tables and create its propertly foreigns keys.
  2. Don't create foreigns key for the columns OriginId and DestinationId and fill it as needed.

Final Question

Is there another way to address this in sql or which of the provided solutions is most aceptable for this scenario?

Community
  • 1
  • 1
Josbel Luna
  • 2,574
  • 3
  • 17
  • 25
  • 1
    Why do area and stowage need to be separate tables? Couldn't you store them in the same table, with `ID` `Name` and `Type` (area/stowage) columns to avoid this issue? – APH Sep 28 '18 at 20:22
  • @APH could be a solution but Stowage and Area has different purposes, different columns (I dind’t add in the example, just let a comment) and brings from different sources – Josbel Luna Sep 28 '18 at 20:54
  • Possible duplicate of [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Sep 28 '18 at 22:29
  • Hi. Things are *subtypes* of other things. Thus SQL/database subtyping/inheritance. An anti-pattern is 2/many/multiple FKs to many/multiple/2 tables. This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Sep 28 '18 at 22:31
  • PS A FK is when a column list's subrow values must appear elsewhere. So you don't "need more than one foreign key per column". You have something that reminds you of a FK. Here you need either multiple columns where each one is a FK and only one is non-NULL or one column that is a FK referencing a column that contains the UNION of the referenced columns of the first case. – philipxy Sep 28 '18 at 22:45
  • It may simplify in the long run to maintain a single table with IDs and Names, and reference those IDs in other places, including where you're storing the other attributes in the tables. Otherwise, everywhere you want to reference an ID you'll also have to indicate what type of place it is. – APH Sep 28 '18 at 23:18
  • @philipxy I know actually about inheritance in database, as an example said in the posible solution, the problem is the most aceptable way of inheritance to this particulary scenario, as I said in my question. – Josbel Luna Oct 01 '18 at 13:21

2 Answers2

1

Tricky. You have 4 foreign keys, so I would [naturally] create 4 foreign key columns, as in:

create table movement (

  origin_area int,
  origin_stowage int,
  dest_area int,
  dest_stowage int,

  constraint fk1 foreign key origin_area references area (id),
  constraint fk2 foreign key origin_stowage references stowage (id),
  constraint fk3 foreign key dest_area references area (id),
  constraint fk4 foreign key dest_stowage references stowage (id),

  constraint chk_fk1 check (origin_area is null and origin_stowage is not null
                         or origin_area is not null and origin_stowage is null),
  constraint chk_fk2 check (dest_area is null and dest_stowage is not null
                         or dest_area is not null and dest_stowage is null)
);

Now, as you see:

  • There are 4 nullable FK columns.
  • Each FK column has its corresponding FK constraint.
  • Also, origin_area and origin_stowage are mutually exclusive. Always one of them is null, while the other points to the other table. This is enforced by the constriaint chk_fk1.
  • The same can be said for dest_area and dest_stowage. Enforced by chk_fk2.
The Impaler
  • 45,731
  • 9
  • 39
  • 76
0

My first thought is something like this:

Create Table MovementEndpoint
(
    ID Int
    , Name Varchar(50)
    , EndpointType Int              -- Area, Stowage, etc
    , EndpointDetailID Int          -- FK to Area, Stowage, etc
)

Now your movements just go between endpoints, and the MovementEndpoint record lets you get to the Area or Stowage record as needed. The query logic will still be a little tricky, but no more so than your initial design requires.

Brian
  • 1,238
  • 2
  • 11
  • 17