Create database scheme for Packages, Products and Services

Transact-SQL
1

Hello,

I have 3 tables: Packages, Products and Services:

create table dbo.Packages ( 
  Id int identity not null
  Name nvarchar (80) not null
)

create table dbo.Products ( 
  Id int identity not null
  Name nvarchar (80) not null
)

create table dbo.Services ( 
  Id int identity not null
  Name nvarchar (80) not null
)

I need to relate the 3 tables to fulfill the following requirements:

  1. A package consists of a mix of products and services;
  2. All products can be included in a package;
  3. Not all services can be included in a package;
  4. A product or service can be included in many packages;
  5. A product or a service cannot appear twice in the same package.

I am not sure how to build this scheme specially because of (3) and (5).

Probably I will need Table inheritance, Many to Many relationship and some kind of Index for (5)?

Could someone, please, advice me on this?

Thank You,
Miguel

2

Hi Shapper,

Will the below database schema be of some help to you ::

create table dbo.Packages (
Id int identity not null,
Name nvarchar (80) not null,
Product_id int references dbo.Products(Id) not null,
services_id int references dbo.Services(ID) not null check( Services_id >=20)
)

create table dbo.Products (
Id int identity not null Primary Key,
Name nvarchar (80) not null
)

create table dbo.Services (
Id int identity not null Primary Key,
Name nvarchar (80) not null

)

regards
Anna

3

One package can have more than one service or one product.