August 26, 2019 at 5:50 pm
Hello,
I am creating a database that will store checkbox values and will need to be later displayed on a web page.
We want to display the Language(s) a user can speak. Therefore we will have the languages listed on an Entry Form as checkboxes, then want to display the selected values on a web page for View later. To do this, I have created multiple fields for each language we would like to list in a table called "UserTbl".
To display the values in a single sting on a web page later, what/how id the best way to do this?
For example, for each user I would like to display the following:
-- The following is my current table structures:
CREATE TABLE #UserTbl
(
TestID int identity (1,1),
Lng_English int,
Lng_Spanish int,
Lng_Chinese int,
Lng_Tagalog int,
Lng_Viet int,
Lng_Arabic int,
Lng_French int,
Lng_Korean int,
Lng_Russian int,
Lng_German int
)
INSERT INTO #UserTbl (Lng_English, Lng_Spanish, Lng_Chinese, Lng_Tagalog, Lng_Viet, Lng_Arabic, Lng_French, Lng_Korean, Lng_Russian, Lng_German)
VALUES (1,0,0,0,0,0,0,0,0,0)
INSERT INTO #UserTbl (Lng_English, Lng_Spanish, Lng_Chinese, Lng_Tagalog, Lng_Viet, Lng_Arabic, Lng_French, Lng_Korean, Lng_Russian, Lng_German)
VALUES (1,1,0,0,0,0,0,0,0,0)
INSERT INTO #UserTbl (Lng_English, Lng_Spanish, Lng_Chinese, Lng_Tagalog, Lng_Viet, Lng_Arabic, Lng_French, Lng_Korean, Lng_Russian, Lng_German)
VALUES (1,0,0,0,0,0,0,0,1,0)
INSERT INTO #UserTbl (Lng_English, Lng_Spanish, Lng_Chinese, Lng_Tagalog, Lng_Viet, Lng_Arabic, Lng_French, Lng_Korean, Lng_Russian, Lng_German)
VALUES (0,1,0,0,0,0,0,0,1,0)
INSERT INTO #UserTbl (Lng_English, Lng_Spanish, Lng_Chinese, Lng_Tagalog, Lng_Viet, Lng_Arabic, Lng_French, Lng_Korean, Lng_Russian, Lng_German)
VALUES (0,0,1,0,0,0,0,0,1,0)
The following is the desired output:
-- UserID Language(s)
-- 1 English
-- 2 English, Spanish
-- 3 English, Russian
-- 4 Spanish
-- 5 Chinese
Do I want to create a LookUp table? Or do I want to do a CASE statement within the SQL to hard code the values and then a STUFF to list them together?
August 26, 2019 at 6:22 pm
Typically one would use a bit value setting to reduce space usage. For example:
languages int NOT NULL
You'd want a separate table with the language code values:
CREATE TABLE #language_codes ( language_bit int NOT NULL, language nvarchar(50) NOT NULL );
INSERT INTO #languages_codes VALUES(1, N'English'), (2,N'Chinese'),(4,N'Tagalog'),(8,...)
For multiple languages, you simply add up the language bit values.
INSERT INTO #UserTbl (languages) SELECT SUM(language_bit) FROM #language_codes WHERE language IN (N'English')
INSERT INTO #UserTbl (languages) SELECT SUM(language_bit) FROM #language_codes WHERE language IN (N'English', N'Tagalog')
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 26, 2019 at 7:14 pm
You've designed this in such a way that it's cumbersome to add new languages. Checkboxes requires you to update your web form and having a separate column requires you to update your table. It's a bad design all around.
You would be better off having a subform (or multi-select dropdown) that allows you to specify multiple languages in one spot and having separate rows for each person/spoken language combination.
You would then use something like XML Concatenation to display the languages.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 26, 2019 at 7:37 pm
This is interesting, how would I do this? Would I still have a field for each option? Or would I store a string of values into 1 table?
August 26, 2019 at 7:46 pm
August 26, 2019 at 8:03 pm
Thanks again! So are you saying the table structure in my example would be OK for data storage, but instead of using checkboxes at the UI level, use a mutliple checkbox drop down?
CREATE TABLE #UserTbl
(
TestID int identity (1,1),
Lng_English int,
Lng_Spanish int,
Lng_Chinese int,
Lng_Tagalog int,
Lng_Viet int,
Lng_Arabic int,
Lng_French int,
Lng_Korean int,
Lng_Russian int,
Lng_German int
)
August 26, 2019 at 8:18 pm
Typically one would use a bit value setting to reduce space usage. For example:
languages int NOT NULL
You'd want a separate table with the language code values:
CREATE TABLE #language_codes ( language_bit int NOT NULL, language nvarchar(50) NOT NULL );
INSERT INTO #languages_codes VALUES(1, N'English'), (2,N'Chinese'),(4,N'Tagalog'),(8,...)
For multiple languages, you simply add up the language bit values.
INSERT INTO #UserTbl (languages) SELECT SUM(language_bit) FROM #language_codes WHERE language IN (N'English')
INSERT INTO #UserTbl (languages) SELECT SUM(language_bit) FROM #language_codes WHERE language IN (N'English', N'Tagalog')
...
This approach seems so archaic to me. In addition, it violates 1st Normal Form.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 26, 2019 at 8:39 pm
Thanks again! So are you saying the table structure in my example would be OK for data storage, but instead of using checkboxes at the UI level, use a mutliple checkbox drop down?
CREATE TABLE #UserTbl
(
TestID int identity (1,1),
Lng_English int,
Lng_Spanish int,
Lng_Chinese int,
Lng_Tagalog int,
Lng_Viet int,
Lng_Arabic int,
Lng_French int,
Lng_Korean int,
Lng_Russian int,
Lng_German int
)
No. separate rows means separate rows. Your table should consist of two columns person (identifier) and language (identifier). Ideally, you would have two foreign keys with one referencing the person table and the other referencing the language table, but the example below uses descriptions rather than foreign keys.
Adam English
Adam French
Adam German
Boris English
Boris Russian
Boris Ukranian
Camille French
Camille Korean
Your original design violates 3rd Normal Form. This redesign complies with 3rd Normal Form.
Drew
PS: You may want to add other columns as long as they pertain to the combination of the person/spoken language. Such attributes may be things like written or spoken fluency, etc.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 26, 2019 at 10:43 pm
ScottPletcher wrote:Typically one would use a bit value setting to reduce space usage. For example:
languages int NOT NULL
You'd want a separate table with the language code values:
CREATE TABLE #language_codes ( language_bit int NOT NULL, language nvarchar(50) NOT NULL );
INSERT INTO #languages_codes VALUES(1, N'English'), (2,N'Chinese'),(4,N'Tagalog'),(8,...)
For multiple languages, you simply add up the language bit values.
INSERT INTO #UserTbl (languages) SELECT SUM(language_bit) FROM #language_codes WHERE language IN (N'English')
INSERT INTO #UserTbl (languages) SELECT SUM(language_bit) FROM #language_codes WHERE language IN (N'English', N'Tagalog')
...
This approach seems so archaic to me. In addition, it violates 1st Normal Form.
Drew
Yes, a compromise to gain performance in the real world. And to match the original check-box idea. I think check boxes are ok for something like this. Put the main language or 2 first, then put the rest in alpha order. Shouldn't be a bid deal to check off the one(s) you need. To pass this info back to an app near the client, are you going to send a string list of language names? Ugh, that seems worse to me, I'd rather encode them.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 27, 2019 at 12:41 am
To pass this info back to an app near the client, are you going to send a string list of language names? Ugh, that seems worse to me, I'd rather encode them.
As I'm sure you are aware, a web page can easily pass back an array of selected code values.
While your design is elegant and minimise storage, it does so at the expense of ease of use, in my opinion.
Imagine asking the new SQL dev for a list of people who can speak both Tagalog and French, but not Russian.
He/she goes to investigate and finds that there's a single LanguagesSpoken INT to be decoded. Compare that with the bread & butter query which Drew's method would require to achieve the same thing.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 27, 2019 at 1:42 pm
Thank you all for your thoughts/recommendations/feedback. I very much like the idea of using a Multiple Checkbox drop down. If I go with these plan, what is the best way to store this in a database? I am a bit confused on how to do this part. Could someone provide an example or confirm if the sample I provided earlier (e.g. #UserTbl) is correct?
August 27, 2019 at 2:04 pm
This is the sort of thing:
DROP TABLE IF EXISTS #User;
DROP TABLE IF EXISTS #Language;
DROP TABLE IF EXISTS #UserLanguage;
CREATE TABLE #User
(
UserId INT NOT NULL PRIMARY KEY CLUSTERED
,Firstname VARCHAR(100)
,Surname VARCHAR(100)
);
CREATE TABLE #Language
(
LanguageCode INT NOT NULL PRIMARY KEY CLUSTERED
,LanguageName NVARCHAR(200)
);
CREATE TABLE #UserLanguage
(
UserId INT NOT NULL
,LanguageCode INT NOT NULL
);
INSERT #User
(
UserId
,Firstname
,Surname
)
VALUES
(1, 'Hugh', 'Jorgan')
,(2, 'Mike', 'Litoris');
INSERT #Language
(
LanguageCode
,LanguageName
)
VALUES
(1, N'French')
,(2, N'Chalcatongo');
INSERT #UserLanguage
(
UserId
,LanguageCode
)
VALUES
(1, 1)
,(1, 2)
,(2, 2);
SELECT u.UserId
,u.Firstname
,u.Surname
,l.LanguageName
FROM #UserLanguage ul
JOIN #Language l
ON l.LanguageCode = ul.LanguageCode
JOIN #User u
ON u.UserId = ul.UserId;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 27, 2019 at 4:24 pm
Many Thanks! Last question, for the Junction table do I want to store a record for all possible language per user? Or only the languages they have selected only?
August 27, 2019 at 4:34 pm
Many Thanks! Last question, for the Junction table do I want to store a record for all possible language per user? Or only the languages they have selected only?
Only those selected for the user.
All possible languages are stored in the Language table.
By the way, I did not include foreign key constraints in my example, but these should certainly also be added from the UserLanguage table to the User and Language tables.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
August 27, 2019 at 4:38 pm
I think a smallint would suffice for language code. I can't imagine there being more than 64K languages (including negative values if absolutely necessary).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply