help with creat a table - SQL Server Forums

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

New to SQL Server Administration

help with creat a table

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 MVP
http://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 preference

3) You could have another table containing the client's preferences

Tell 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 Kristen

I 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       Red
Kristen 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       Red
Kristen 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 Blue

And 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 Clients
WHERE    [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 do


SELECT ...
FROM Clients AS C
     JOIN Preferences AS P
         ON P.ClientID = C.ClientID
WHERE 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 :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources