question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked BertZhoumsft-7490 edited

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

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

sql-server-generalsql-server-transact-sql
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@ahmedsalah-1628
204349-image.png
Your description is too vague , please explain your needs , preferably with expected answers.

Bert Zhou


0 Votes 0 ·
image.png (4.3 KiB)

i need any one help me to give database design for data i added
so are this correct

Create one table to hold the countries, each country should have a unique id
Create one table to hold what ever this data is meant to be - without the countries column
Create a third table that links them together containing just the ids - each row links just one country to the id from this table, so each row in this table will have 3 rows on your linking table

or create table store text as hashing text as value then compare text hashing on database with hashing value uploaded every time

0 Votes 0 ·

Hi, @ahmedsalah-1628

There is a question here , if you want to design the third table to contain only the ID column , then the first table must contain the ID column and the country two fields , and use the ID of the first table as the primary key , set the third table is the foreign key of the first table .

according to what you said , if you want to store the meaning of these data in a separate text , why should you splicing these countries into the Contries field if you want to compare them ? You still need to separate this field for subsequent operations.

Bert Zhou

0 Votes 0 ·

1 Answer

pituach avatar image
0 Votes"
pituach answered BertZhoumsft-7490 edited

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thank you for reply

until now i don't know how to handle countries sticks

store on table as

countryid|countryid|countryid

or what i do please to handle stick

0 Votes 0 ·

Hi,@ahmedsalah-1628
Is there any additional information that needs to be added to this question? Because I see you seem to have posted a similar question post.
Bert Zhou

0 Votes 0 ·