CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Martin Acero
Martin Acero

Reputation: 1

Is there a way to do this, Not to repeat?

I handle projects by code (PROYECT_A A00001)

There are 3 tables (SEGMENT SE00001,LABLE LA00001,STRONG ST00001) that relate to a project, there may be repeated codes from these tables but they belong only to a project

Is there any way to not repeat the fields?

CREATE TABLE [dbo].[PROYECT_A](
    [PROYECT_A] [int] IDENTITY(1,1) NOT NULL,
    [CODE_A]  AS ('A'+right('00000'+CONVERT([varchar],[PROYECT_A],(0)),(5))),
    [STATUS] [datetime] NOT NULL,
    [CREATION_DATE] [datetime] NOT NULL,
 CONSTRAINT [PK_RR] PRIMARY KEY CLUSTERED 
(
    [CODE_A] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO

CREATE TABLE [dbo].[SEGMENT](
    [ID_SEGMENT] [int] IDENTITY(1,1) NOT NULL,
    [CODE_SE]  AS ('SE'+right('00000'+CONVERT([varchar],[ID_SEGMENT],(0)),(5))),
    [NAME] [varchar](20),
    [POWW] [varchar](20),
    [SQWE] [varchar](20),
    [DATESTART] [datetime] NOT NULL,
    [DATESTART] [datetime] NOT NULL
    [CODE_A] [varchar](20)
GO


CREATE TABLE [dbo].[LABLE](
    [ID_LABLE] [int] IDENTITY(1,1) NOT NULL,
    [CODE_LA]  AS ('LA'+right('00000'+CONVERT([varchar],[ID_LABLE],(0)),(5))),
    [NAME] [varchar](20),
    [POWW] [varchar](20),
    [SQWE] [varchar](20),
    [DATESTART] [datetime] NOT NULL,
    [DATESTART] [datetime] NOT NULL
    [CODE_A] [varchar](20)
GO

CREATE TABLE [dbo].[STRONG](
    [ID_STRONG] [int] IDENTITY(1,1) NOT NULL,
    [CODE_ST]  AS ('ST'+right('00000'+CONVERT([varchar],[ID_STRONG],(0)),(5))),
    [NAME] [varchar](20),
    [POWW] [varchar](20),
    [SQWE] [varchar](20),
    [DATESTART] [datetime] NOT NULL,
    [DATESTART] [datetime] NOT NULL
    [CODE_A] [varchar](20)
GO

Upvotes: 0

Views: 47

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93704

create a single table with Type column to differentiate the records whether it belongs to SEGMENT,LABLE or STRONG. 

CREATE TABLE [dbo].[SEGMENT](
    [ID_SEGMENT] [int] IDENTITY(1,1) NOT NULL,
    [Type] as Varchar(15) -- Here `SEGMENT`,`LABLE` or `STRONG`
    [CODE_SE]  AS (case type 
                     when 'Segment' then 'SE' 
                     when 'LABLE' then 'LA' 
                     else 'ST' 
                   end +right('00000'+CONVERT([varchar],[ID_SEGMENT],(0)),(5))),
    [NAME] [varchar](20),
    [POWW] [varchar](20),
    [SQWE] [varchar](20),
    [DATESTART] [datetime] NOT NULL,
    [DATESTART] [datetime] NOT NULL
    [CODE_A] [varchar](20)
    )

Upvotes: 1

Related Questions