How to design tables to be best practise without repeating data ?

Question

I work on sql server 2017 i have table data as below

i need to make design for data to be best practise and prevent repeating data

my issue here on column countries have data separated sticks so how to handle that

create table #countriesData
(
company int,
[Year] int,
rev  int,
countries varchar(500)
)
insert into #countriesData(company,[Year],rev,countries)
values
(12011,2010,121,'Egypt|France|America'),
(12011,2011,121,'Egypt|Canda|America'),
(12011,2012,121,'China|Canda|America'),
(12099,2010,121,'SaudiArabia|France|America'),
(12099,2011,121,'Egypt|Canda|German'),
(12099,2012,121, 'China|Italy|America')

my key is company and year and rev and not repeated

so How to handle sticks on column countries

Answer 1

Hi,

How to design tables to be best practise without repeating data ?

Why are you assuming that "best practice" means "without repeating data" ?!?

In many cases (like database which is used for data warehouse) best practice means preparing the data for fast reporting. This is done by storing data after aggregation or ETL actions, which mean data which repeat the original data.

have you heard about indexes with include?!? This is a direct case of repeating data.

In other cases we might want to create a unique clustered index on a view which can improves query performance dramatically in some cases!

There are many cases that storing duplicate data is the right way to gain better performance. It might have a lot of other disadvantages and it will not always be the right way, but it might be.

i need to make design for data to be best practise and prevent repeating data

In order to design the database for best practice we need to fully know what are your practices or in other words, how do you use the data.

If we do not know how you use the data then we cannot design the database for best used

Answer 2

Looking at this:

create table #countriesData ( company int, [Year] int, rev int, countries varchar(500) )

insert into #countriesData(company,[Year],rev,countries) values (12011,2010,121,'Egypt|France|America'), (12011,2011,121,'Egypt|Canda|America'), (12011,2012,121,'China|Canda|America'), (12099,2010,121,'SaudiArabia|France|America'), (12099,2011,121,'Egypt|Canda|German'), (12099,2012,121, 'China|Italy|America')

My first thought is that you are wanting:

CREATE TABLE Country(CountryId int NOT NULL PRIMARY KEY, CountryName)
CREATE TABLE CountriesDataCountry(CompanyId int not null, Year int not null, CompanyId int not null, (CompanyId, Year, CompanyId PRIMARY KEY) --not pictured, foreign key constraints :)

(I kept your table name as a prefix to my new many to many table, but I probably change your object name too once I really understood the purpos.