What relationships will the tables have?

Question

Database design

2 tables Users and Images
Table Users:

- ID (primary_key)
- Name
- Photo_id (foreign key)
- Face_id (foreign key)
- Gallery (foreign key)

Table images:

- ID (primary_key)
- Name
- Date

3 elements from the Users table (photo_id, face_id, gallery) must refer to the Images table. Moreover, the gallery consists of many instances of the Images table. What connections are there and how can they be built?

Answer 1

Structure

USE [test]
GO
/****** Object:  Table [dbo].[Galleries]    Script Date: 1/15/2022 11:17:53 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Galleries](
  [id] [uniqueidentifier] NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [UserId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Galleries] PRIMARY KEY CLUSTERED 
(
  [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Images]    Script Date: 1/15/2022 11:17:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Images](
  [Id] [uniqueidentifier] NOT NULL,
  [Name] [nvarchar](200) NOT NULL,
  [Taken] [datetime] NOT NULL,
  [Path] [nvarchar](400) NOT NULL,
  [ParentId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Images] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Users]    Script Date: 1/15/2022 11:17:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
  [Id] [uniqueidentifier] NOT NULL,
  [FirstName] [nvarchar](200) NULL,
  [LastName] [nvarchar](200) NULL,
  [Burth] [datetime] NULL,
  [Sex] [tinyint] NULL,
  [Email] [nvarchar](200) NOT NULL,
 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Galleries] ADD  CONSTRAINT [DF_Galleries_id]  DEFAULT (newid()) FOR [id]
GO
ALTER TABLE [dbo].[Images] ADD  CONSTRAINT [DF_Images_Id]  DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[Users] ADD  CONSTRAINT [DF_Users_Id]  DEFAULT (newid()) FOR [Id]
GO
ALTER TABLE [dbo].[Galleries]  WITH CHECK ADD  CONSTRAINT [FK_Galleries_Users] FOREIGN KEY([UserId])
REFERENCES [dbo].[Users] ([Id])
GO
ALTER TABLE [dbo].[Galleries] CHECK CONSTRAINT [FK_Galleries_Users]
GO

Samples

SELECT TOP 1000 u.Id
      , FirstName 
      , LastName 
      , Burth 
      , Sex 
      , Email ,
    i.Name as ImageName,
    i.Path as ImagePath
  FROM Users as u
  LEFT JOIN Images i on u.id = i.ParentId
GO
SELECT TOP 1000 g.id
      ,Name
      ,UserId,
    u.firstName + ' ' + u.LastName as UserName
  FROM Galleries g
  LEFT JOIN users u on g.UserId = u.id 
GO

SELECT TOP 1000 i.Id
      , i.Name 
      , Taken 
      , Path 
      , ParentId
    , g.Name as GaleryName
    , CASE WHEN g.Name IS NOT NULL THEN 'galery' ELSE 'avatar' END as ImageType
  FROM Images i
  LEFT JOIN Galleries g on i.ParentId = g.id
  order by ParentId
  GO

Answer 2

There should be no mention of pictures in users.
There should be two more columns in pictures, user id and type - photo, user, gallery

Answer 3

Thanks everyone for the replies. Issue resolved. Added type field to Images table. The answer turned out to be simple