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.
| Author |
Topic |
|
neoice
Starting Member
7 Posts |
 Posted - 2009-02-28 : 03:35:16
|
Hi,I have recently created a web for app using VB.net and SQL2005. A problem has arisen and I can see an error in my design but not sure how to resolve it. I am new to DB esign so any expert advice is much appreciated. I will try my best to explain the situation. Here are the first 6 columns of my main table:tblmainID,initials,deptid,code1,code2,code31,JGS,37,F2,E3,B12,ASA,33,A1,null,null3,GGF,19,G1,B2,nulletc........code1,2 and 3 are just columns and not linked to anything.Ok, on the user webform there is a dropdown box which populates itself from 'tblcodes' at run time.TblCodesCodeId,Code1,A12,A2and so on...there are around 60 entries.The user selects a code, which in turn is added to a listbox. The contents of this list box is then fed into the database along with the rest of the data when then form is submitted.During the database design discussions I was told that there would only ever be need for a maxium of 3 codes. The need has now arisen for a 4th code.So, I am looking at my design and thinking it is flawed.I probably should replace code1,2 and 3 with just a codes column but how do i link it to more than one entry? Can you store more than one peice of data in a column for example:codesF2,E3,B1B1,A1A2And if so, could a query be perform on the indivual codes, for example:select * from tblmain where codes ="B1"This is really confusing me and would appreciate some expert help.If you need any more info just ask!Thanks in advance. |
|
|
pootle_flump
1064 Posts |
Posted - 2009-03-06 : 06:39:57
|
quote:
Originally posted by neoice
During the database design discussions I was told that there would only ever be need for a maxium of 3 codes. The need has now arisen for a 4th code.
Heh - we all make that mistake once, and once only First normal form - the correct design is a single codes column, however you don't put CSV data in there.This is a good introduction to relational design and normalisation. Have a read, and particularly concentrate on the part about First Normal Form.http://www.tonymarston.net/php-mysql/database-design.html |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-03-12 : 11:31:49
|
| Strictly speaking you should have a single column for Codes and if more than one is selected create new rows. But to answer your question you can put comma seperated values in a single column. To find an item you would query select * from tblmain where codes like '%B1%'Or you could add the 4th code column - although it breaks normalisation rules if redesigning your website would cause major problems it might be worth compromising on Normalisation. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2009-03-13 : 15:52:04
|
| [url]http://www.inconcept.com/JCM/May2002/pascal.html[/url]You simply can't expect sound advise from an internet forum without having performed any logical modeling and/or at the very least some explanation of the business domain.A multi-valued attribute would be a blatant violation of normalization ... Dr. Codd just can't take any more rolling over in his grave.Without knowing anything about your domain, it seems you have two options.First, if each additional code selection results in a new tblMain proposition, then you should follow the advise to creation a new tuple.Second, if instead, you have a many to many relationship between tblMain and tblCodes, you should create a tblMainCodes{tblMainID,tblCodesID} relation.While you are add it, drop the hungarian notation and add some DRI.Note: relation, tuple, attribute are fancy-pants words that (roughly) translate to table, row, column (or file, record, field for the totally hopeless) ... but I'm using those in the hopes to spark your curiosity about the mathematical and scientific foundations of database design. I'm only partially a douche-bag.Jayto here knows when |
|
|
|
|
|
|
|
|
Advice with design