Does it make sense to define a new “Data type”? Is there a way to do so?

All we need is an easy explanation of the problem, so here it is.

I’m basically new to SQL, and I was making my own project when this question came to my mind.
I’m making a database that has an entity where an attribute would be a "number" and a "year".
In many programming languages one would create a new object or data type that takes both the number and the year. (say a tuple with INT, YEAR)
I wanted to know if this makes sense in SQL or I should just go with an INT and YEAR attribute.

To put more details into the project, I’m creating a DB for my mom who runs a business that goes with "campaings" (the INT value) directly associated with the "Year", so one would put the campaign 09 for year 2020, and so on.
I wanted to create a new Data Type for the attribute so then I don’t have multiple "campaign 09" with different years.

I hope my question is clear, thanks!

EDIT: Read the comments for the answer.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This isn’t something that requires a custom data type to solve.

If the requirement is Campaign 09 can only be associated with one year, then Year is simply an attribute for Campaign (although I’d argue perhaps StartDt or something similar, from which year can be derived might be more appropriate).

To illustrate:

CREATE TABLE Campaign
(
  CampaignNbr  INT   NOT NULL
 ,StartDt      DATE  NOT NULL
 /* Everything else */
 ,CONSTRAINT PK_Campaign PRIMARY KEY (CampaignNbr)
)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply