Author |
Topic |
morrgray
Starting Member
7 Posts |
Posted - 2011-09-23 : 06:47:54
|
Hi I am just begined to study SQL, I got an excise,a table with columns 'client_name', 'client_no",and 'client_preference', which i can only insert one preference for each client, but the excise wants me to alter the table then each client can express more then one preference, how can i do that ?Thank you in advance  |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-23 : 06:50:33
|
sorry cant provide answers for exercise since you wont be gaining anything by that. Search for table constraints in google and then you will get an answer for this------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
Kristen
Test
22859 Posts |
Posted - 2011-09-23 : 06:55:42
|
1) You could store the Preferences in the [client_preference] column as "1,2,3,4"2) You could add more columns to the table for each preference3) You could have another table containing the client's preferencesTell us what you think the Pros and Cons are of each and we can guide you along the way. |
 |
|
morrgray
Starting Member
7 Posts |
Posted - 2011-09-23 : 07:25:08
|
Thanks KristenI think the first subjection sounds more like the solution for the exercise. Can you show me how I should do it? |
 |
|
morrgray
Starting Member
7 Posts |
Posted - 2011-09-23 : 07:30:13
|
Sorry i meant suggestion |
 |
|
Kristen
Test
22859 Posts |
Posted - 2011-09-23 : 07:45:22
|
Ah, well there you have fallen at the first hurdle If that is the solution then your teacher won't get a lot of sympathy on this forum!The problem with the first solution is that you can't easily query "Clients who have Preference=2" - because the [client_preference] column might be "2", or "1,2" or "1,2,3" or "1,222,3". You can do string comparison tests to match the data, but its all very messy (and a very inefficient way for a relational database).Also, how big do you make the [client_preference] column? Whatever size you choose you will either be potentially wasting space, or imposing a limit on the number of preferences that the column can hold. (I'm sure there is a finite number in your current example, but consider it in the wider context of "best practice")Do you have any thoughts about why (2) is better than (3) - or the other way round? |
 |
|
Kristen
Test
22859 Posts |
Posted - 2011-09-23 : 07:50:00
|
Actually there is a 4th way:You could store multiple rows:client_name client_no client_preference=========== ========= =================Kristen X 123 RedKristen X 123 Blue this falls foul of Normalisation rules (which hopefully your teach has, or will, teach you about), in a nutshell what happens when you have:client_name client_no client_preference=========== ========= =================Kristen X 123 RedKristen Y 123 Blue which of my names is correct? Thus you should only store my name once in a record for client_no=123, then there cannot be any duplicates, and there will not be any ambiguity that multiple repeat-data would be at risk of. |
 |
|
morrgray
Starting Member
7 Posts |
Posted - 2011-09-23 : 08:19:11
|
It seens like I have to discard the first soultion, maybe the second is more suitable as I am not allow to make more tables then this. but is it possible to make AUTO_INCREMENT to columns instead of rows?like:client_name client_no client_preference 1 Client_preference 2 =========== ========= ================= ===================Kristen X 123 Red BlueAnd so on, as I dont know how many preferences there will be.And I havnt studied Normalisation rules yet and my teacher sucks, he does not thing, but telling us to help each other, he doesn't have the solution. |
 |
|
Kristen
Test
22859 Posts |
Posted - 2011-09-23 : 08:59:15
|
"I am not allow to make more tables then this"Ah "my teacher sucks"Oh dear Well the correct solution is 3 because it is scalable. A client can have as many preferences as they need.If you add columns you are setting a limit to the number of preferences, plus you have to remember to check them all:SELECT ...FROM ClientsWHERE [Client_Preference1] = '2' OR [Client_Preference2] = '2'... and if you add another [Client_Preference3] column then you have to change the code wherever [Client_Preference1], [Client_Preference2] ... is used. It makes the system very fragile because if you forget to change one then there will be a bug.Instead: if you have a second "preferences" table you can just doSELECT ...FROM Clients AS C JOIN Preferences AS P ON P.ClientID = C.ClientIDWHERE P.[Preference] = '2' and you have an unlimited number of preferences-per-client, and thus a scalable solution.Dunno what to advise you to do for your assignment. Cut & Paste this to your work, tidy it up a bit an hand that in?So long as you have understood the choices, and why some are bad etc., then I reckon you've completed this lesson successfully |
 |
|
morrgray
Starting Member
7 Posts |
Posted - 2011-09-25 : 14:41:42
|
Thank you so much, Kirsten, you are so helpfuld :) |
 |
|
|