| Author |
Topic |
|
msadiqrajani
Starting Member
6 Posts |
 Posted - 2010-02-17 : 09:33:36
|
| Hi , readersLet me clearly explain what i need.I have a table Name People with fields.ID______firstname____lastname_____etc_____and qualitiesAll 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
msadiqrajani
Starting Member
6 Posts |
Posted - 2010-02-17 : 09:58:54
|
| Thanks for reply.I have some qualities likeQuality1Quality2Quality3Quality4Quality5in my table i am insertting values likein 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. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
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 |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
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).ThanksRegards,Sadiq Rajani. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
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.comOnce again thanks..............I will surely attach with this forum.Seeking advice and helping others. |
|
|
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
msadiqrajani
Starting Member
6 Posts |
Posted - 2010-02-17 : 11:09:08
|
| I will test and reply what i get. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-17 : 11:12:18
|
| ok cool------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Which Datatype to use in this scenario........