Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Which Datatype to use in this scenario........

Author  Topic 

msadiqrajani
Starting Member

6 Posts

Posted - 2010-02-17 : 09:33:36
Hi , readers

Let me clearly explain what i need.
I have a table Name People with fields.

ID______firstname____lastname_____etc_____and qualities


All fields are clear,I only need to explain Qualities.

I need an array of string in this column and am confused which data type to use.Currently i am using a text data type and insertting a value in a deliminated text.

I want to conform if this is a good idea to use this or sqlserver have another specific data type.

i have heard of xml data type,but frankly i am not sure how to use this data type to achieve a desired result.

I think sqlserver allows us to programm custom data type, but again frankly i have never used it.

Please help me what to do in this scenario.Is deliminated text idea is good or should i turn to another direction.

One more thing,laterly i will like to bind a table with VB.net application with CheckedListBox control.

Again with my deliminated idea,i have done good so far,but i need a clean one technique.

Thanks in advance.

Regards,
Sadiq Rajani.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 09:36:22
how will you be using the field value in queries? what exactly will you be storing in it? In any case text is not a good selection if you're using sql 2005 or later as its deprecated. you need to choose b/w varchar(max) or xml i guess.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

msadiqrajani
Starting Member

6 Posts

Posted - 2010-02-17 : 09:58:54
Thanks for reply.

I have some qualities like

Quality1
Quality2
Quality3
Quality4
Quality5

in my table i am insertting values like

in person1 value "Quality1;Quality4"
in person3 value "Quality1;Quality3;Quality5"

so in my application i have checked list box listed with all qualities.
And according to person qualities it checks a Value in control.

It is working perfect when i am not filtering records by qualites.But to filter bya quality and multiple quality i am stick.

So how xml be work in this scenario and what is var(max).I have never used any of this.

If these are the only solution,then please spot a light on these topic or refer some other source on this topic.

Person example is just a simple one, laterly i will apply this technique in my application.

Thanks.
Regards,
Sadiq Rajani.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 10:02:42
why cant you place qualities in a separate table with fields QualityID ,QualityName. Then keep a mapping table to store relationship. ie. it will have PersonQualityID which will be PK, PersonID and QualityID so for person with multiple qualities it will have more than record indicating 1 to many relation.You can always filter on qualities of person by joining between tables on related id columns.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

msadiqrajani
Starting Member

6 Posts

Posted - 2010-02-17 : 10:28:49
Surely i cant use multiple records for 1 entry.as i have told u than i will use this technique later in developing realworld application and surely this application will have more than 1 lacs, so 1 lacs by multiple quality,........no ,i cant do this......

what about filtering a record using substring type function.Sqlserver string filter will help me i think. other useful technique will be welcomed
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 10:31:53
even if you've 1 lakh records, will you be accessing all of them in your application?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

msadiqrajani
Starting Member

6 Posts

Posted - 2010-02-17 : 10:40:42
No. application will load just 1 to assuming < 100 records.i am just wary of database size.it will increse many time more.


Your idea for multiple records have toched me and i will surely give it a try by adding dynamic 100000 records.So thank's for this.

One last think, even my application will not load so many record,should i dont vary about database rapidly incresing size.

And is there less painfull Idea.My brain still hungry of XML,and VAR(MAX).


Thanks
Regards,
Sadiq Rajani.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 10:47:31
1 lakh records is not that big a size for table.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

msadiqrajani
Starting Member

6 Posts

Posted - 2010-02-17 : 11:04:19
Yes visakh16......Just worring because for 1 lacks , i will have to add many more lacks to database and this is for just 1 coloum field,and in application design i have 3 columns,so it means for i record i have to add many time more records.

Any way currently i am applying your idea as it is seems less painfull then mine, as will easily filter records. iam testing this by adding 100000 records to database which will add many more records.if it performs good then i will apply it in my application unless i get information about VAR(MAX) and XML.

Iam applying your multiple table idea,so hats off.
Also thanks to sqlteam.com

Once again thanks..............
I will surely attach with this forum.Seeking advice and helping others.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 11:06:59
The problem with your initial approach is again you've to parse and split up individual values before you do join,search etc which is also an overhead for large data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

msadiqrajani
Starting Member

6 Posts

Posted - 2010-02-17 : 11:09:08
I will test and reply what i get.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-17 : 11:12:18
ok cool

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -