-1

We have four tables for Departments, Divisions, Position and Profile. Profile can have multiple sets of this. This is kinda many-to-many relation. We trying to figure out how to implement this in the right way.

For now we want to create table that has FK's for this four tables. Is this right implementation?

Here is T-SQL query:

if(not exists (select null from sys.tables where name ='Departments'))
CREATE TABLE Departments
(
    DepartmentsId int primary key identity(1,1),
    Name  nvarchar(50)

)

go

if(not exists (select null from sys.tables where name ='Positions'))
CREATE TABLE Positions
(
    PositionsId int primary key ,
    Name nvarchar(50)
)
go

if (not exists(select null from sys.tables where name = 'Divisions'))
CREATE TABLE Divisions
(
    DivisionsId int primary key identity(1,1),
    Name nvarchar(50)
)

go


if (not exists(select null from sys.tables where name = 'Administrations'))
CREATE TABLE Administrations
(
    AdministrationsId int PRIMARY KEY,
    Name nvarchar(50),
    Value int
)


if (not exists(select null from sys.tables where name = 'Profiles'))
CREATE TABLE Profiles
(
    ProfilesId int PRIMARY KEY not null,
    FirstName nvarchar(50),
    LastName nvarchar(50),
    DateOfBirth date,
    ProfileImage varchar(100),
    AdministrationsId int,
    CONSTRAINT FK_Administration FOREIGN KEY (AdministrationsId) REFERENCES  Administrations (AdministrationsId)
)

go

if (not exists(select null from sys.tables where name = 'ProfilesPositions'))
CREATE TABLE ProfilesPositions
(
    ProfilesId int not null foreign key references Profiles(ProfilesId),
    PositionsId int not null foreign key references Positions(PositionsId),
    DivisionsId int not null foreign key references Divisions(DivisionsId),
    DepartmentsId int not null foreign key references Departments(DepartmentsId),
    CONSTRAINT PK_ProfilesPositions primary key(ProfilesId,PositionsId,DivisionsId,DepartmentsId)
)

Also we are not sure, should we create relations department-division (one-to-many), division-position (many-to-many) and department-position (one-to-many). This, as I think, will be less flexible. But if it will be easier to implement, then we will try to make this way.

9ghtX
  • 1
  • 1
  • 1
    Your SQL appears to have some syntax errors. `CREATE` and `SELECT` and `FROM` are single words; you have `CRE ATE` and `FR OM`, for example. – Thom A Aug 31 '23 at 10:33
  • Should there be a connection between department, division and position, or are these unrelated? – Zohar Peled Aug 31 '23 at 10:45
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Aug 31 '23 at 18:33
  • @ZoharPeled, we are not sure, should we create relations department-division (one-to-many), division-position (many-to-many) and department-position (one-to-many). This is part of our question. I'll add to body – 9ghtX Sep 01 '23 at 03:39
  • Only you know the structures you want to model in your database. One of the main tasks ofa relational database is to ensure consistency. For this to happen, it must be modelled appropriately. It is very common in companies that there are divisions and the divisions are subdivided in departments. In that case the database should contain this 1:n relation. If on the other hand a department can belong to more than one division, you need an m:n relation. Only in the unlikely situation that divisions and departments are two structures that have no relation, you would make them standalone. – Thorsten Kettner Sep 01 '23 at 08:43
  • Same for positions. If a position is 'Accountant', does this position only exist in the 'Accounting' department? If so, the database should represent this, if not then not. So look at the real world you want to model and look for all relations and use them in your database. – Thorsten Kettner Sep 01 '23 at 08:45
  • @ThorstenKettner, okay, from what you say I understand we can make this relations as we want. There is no *right* way to make this. – 9ghtX Sep 01 '23 at 09:20
  • @ThorstenKettner, here is scheme of our DB - https://imgur.com/a/R0YHaoJ – 9ghtX Sep 01 '23 at 09:32
  • Forget about the database and think only about what the data actually represents. In the real life (assuming, of course, your data is modeled to represent real life information), what is the relationship between position and department, for instance? – Zohar Peled Sep 01 '23 at 20:50

0 Answers0