Data Modeling (Relay Scale System)

Asked

Viewed 62 times

1

I’m developing a relay scale system.

Think of it as a kind of calendar, where one day has multiple shifts, shift has multiple positions, and employee is scaled to the position.

Employee may be on call or off, if you’re on call, I need to know what day, what shift, and what position. I’ve already modeled the strong entities. But I’m having a hard time modeling relationships.

I thought of an entity "scale", where there would be the intersection of day, shift, position and employee. However, I have encountered some problems, for example, I can scale the same employee for the same shift and position on the same day, or I can scale two employees to the same position on the same shift and same day.

I enclose an outline of the conceptual model.
Obs: I know that by the rules, N:M relations generate an associative entity. However I do not understand how these entities would relate to the others.

If you have someone who knows a lot about modeling and is willing to help.

DER

Follow the sqlverver script:

CREATE TABLE ABSENCE (
  idABSENCE INTEGER CHECK (idABSENCE > 0) NOT NULL IDENTITY,
  NICKNAME_ABS VARCHAR(4) NOT NULL,
  REASON_ABS VARCHAR(max) NULL,
  PRIMARY KEY(idABSENCE)
);

CREATE TABLE DESK (
  idDESK INTEGER CHECK (idDESK > 0) NOT NULL IDENTITY,
  TYPE_DESK_idTYPE_DESK INTEGER CHECK (TYPE_DESK_idTYPE_DESK > 0) NOT NULL,
  NICKNAME_DES VARCHAR(3) NOT NULL,
  DESCRIPTION_DES VARCHAR(max) NULL,
  PRIMARY KEY(idDESK)
);

CREATE INDEX FK_TYP_DESK ON DESK(TYPE_DESK_idTYPE_DESK);

CREATE TABLE EMPLOYEE (
  idEMPLOYEE INTEGER CHECK (idEMPLOYEE > 0) NOT NULL IDENTITY,
  SHIFT_idSHIFT INTEGER CHECK (SHIFT_idSHIFT > 0) NOT NULL,
  LICENCE_idLICENCE INTEGER CHECK (LICENCE_idLICENCE > 0) NOT NULL,
  TEAM_idTEAM INTEGER CHECK (TEAM_idTEAM > 0) NOT NULL,
  NAME_EMP VARCHAR(20) NOT NULL,
  LASTNAME_EMP VARCHAR(30) NOT NULL,
  NICKNAME_EMP VARCHAR(15) NOT NULL,
  TRIGRAM_EMP VARCHAR(3) NULL,
  USER_EMAIL VARCHAR(40) NOT NULL,
  USER_PASSWORD VARCHAR(15) NOT NULL,
  POST_EMP VARCHAR(15) NOT NULL,
  PRIMARY KEY(idEMPLOYEE)
);

CREATE INDEX FK_TEA ON EMPLOYEE(TEAM_idTEAM);
CREATE INDEX FK_LIC ON EMPLOYEE(LICENCE_idLICENCE);
CREATE INDEX FK_PRIM_SHI ON EMPLOYEE(SHIFT_idSHIFT);
CREATE INDEX FK_SEC_SHI ON EMPLOYEE(SHIFT_idSHIFT);

CREATE TABLE EQUIPMENT (
  idEQUIPMENT INTEGER CHECK (idEQUIPMENT > 0) NOT NULL IDENTITY,
  BRAND_EQU VARCHAR(20) NOT NULL,
  MODEL_EQU VARCHAR(20) NOT NULL,
  PRIMARY KEY(idEQUIPMENT)
);

CREATE TABLE LICENCE (
  idLICENCE INTEGER CHECK (idLICENCE > 0) NOT NULL IDENTITY,
  DATEOFISSUE_LIC DATETIME2(0) NOT NULL,
  VALIDTO_LIC DATETIME2(0) NOT NULL,
  PRIMARY KEY(idLICENCE)
);

CREATE TABLE LIC_EQU (
  LICENCE_idLICENCE INTEGER CHECK (LICENCE_idLICENCE > 0) NOT NULL,
  EQUIPMENT_idEQUIPMENT INTEGER CHECK (EQUIPMENT_idEQUIPMENT > 0) NOT NULL,
  PRIMARY KEY(LICENCE_idLICENCE, EQUIPMENT_idEQUIPMENT)
);

CREATE INDEX FK_LIC ON LIC_EQU(LICENCE_idLICENCE);
CREATE INDEX FK_EQU ON LIC_EQU(EQUIPMENT_idEQUIPMENT);

CREATE TABLE SCHEDULE (
  idSCHEDULE INTEGER CHECK (idSCHEDULE > 0) NOT NULL IDENTITY,
  SHIFT_idSHIFT INTEGER CHECK (SHIFT_idSHIFT > 0) NOT NULL,
  DESK_idDESK INTEGER CHECK (DESK_idDESK > 0) NOT NULL,
  ABSENCE_idABSENCE INTEGER CHECK (ABSENCE_idABSENCE > 0) NULL,
  EMPLOYEE_idEMPLOYEE INTEGER CHECK (EMPLOYEE_idEMPLOYEE > 0) NULL,
  DATE_EXEC DATETIME2(0) NOT NULL,
  SCH_STATUS INTEGER CHECK (SCH_STATUS > 0) NOT NULL,
  PRIMARY KEY(idSCHEDULE)
);

CREATE INDEX FK_EMP ON SCHEDULE(EMPLOYEE_idEMPLOYEE);
CREATE INDEX FK_ABS ON SCHEDULE(ABSENCE_idABSENCE);
CREATE INDEX FK_DESK ON SCHEDULE(DESK_idDESK);
CREATE INDEX FK_SHI ON SCHEDULE(SHIFT_idSHIFT);

CREATE TABLE SHIFT (
  idSHIFT INTEGER CHECK (idSHIFT > 0) NOT NULL IDENTITY,
  NICKNAME_SHI VARCHAR(4) NOT NULL,
  START_SHI DATETIME2(0) NOT NULL,
  FINISH_SHI DATETIME2(0) NOT NULL,
  PRIMARY KEY(idSHIFT)
);

CREATE TABLE TEAM (
  idTEAM INTEGER CHECK (idTEAM > 0) NOT NULL IDENTITY,
  NAME_TEA VARCHAR(20) NULL,
  PRIMARY KEY(idTEAM)
);

CREATE TABLE TYPE_DESK (
  idTYPE_DESK INTEGER CHECK (idTYPE_DESK > 0) NOT NULL IDENTITY,
  NAME_TYP_DES VARCHAR(15) NOT NULL,
  PRIMARY KEY(idTYPE_DESK)
);

I have already researched the literature, but the ternary relations I meet did not help me very much, so I would like to know how I identify the possible ternary relations in this diagram.

  • Hello @Isaias Nori welcome to Stack Overflow! The OS is a programming community, be more specific in your question, show what you tried and give us a reproducible example of your code so we can help you.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.