How can I generate a key for this column which is part of a database model?
-
I am normalizing a larger table. Here is a screenshot showing part of the relationship of the normalized table:
I'm using MS SQL Server and SSMS.
The plan was to create the empty tables then insert values from the denormalized table. I started doing so until I realised that I hadn't generated 'Type codes' in the original table. I'm a newbie so I wasn't sure how it's done traditionally. I believe that I'm supposed to use CASE function to generate codes based on certain values but then again I could be well off. I'd kindly like to ask how I can do this correctly?.
Thank you
-
There are two main approaches:
(1) use a generic number for all codes, typically generated automatically by an identity column
So, type code = 1 might be 'Issue', = 2 might be 'Research Task', etc.
(2) use an alphanumeric code that's based on the actual value
So, type code = 'I' might be 'Issue', 'R' might be 'Research Task', etc.
Typically (1) is used. (2) is used only if it is already part of normal business activity. For example, you already have 1-3 char CPZ zones, so you would use those zones as they already and not assign a new number.
Say you decide to use numerics for Type:
CREATE TABLE dbo.Type ( type_code smallint NOT NULL IDENTITY(1, 1), type varchar(50) NOT NULL )
INSERT INTO dbo.Type (type ) VALUES('Issue'), ('Research Task')
For a ticket with a type of 'Issue', you would need to INSERT 1 into column Type Code on Ticket.
INSERT INTO Ticket ( ..., [Type Code], ... )
SELECT ..., (SELECT [Type Code] FROM Type WHERE type = 'Issue') AS [Type Code], ...
Relational tables should intentionally be designed this way, with lookup tables rather than hard-coded values in tables. It may seem awkward to you at first, but this is the only proper way to design such tables.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Hi Scott. Thanks for your reply. I understood most of what you said. I'd kindly like to ask for some clarification on some parts. But here is what I understood from these steps (please correct me if I'm wrong):
"INSERT INTO dbo.Type (type ) VALUES('Issue'), ('Research Task') You inserting two values into the 'type' column. I imagine that the 'type code' in this table is already generated as 1,2 respectively based on the line "type_code smallint NOT NULL IDENTITY(1, 1)".
For a ticket with a type of 'Issue', you would need to INSERT 1 into column Type Code on Ticket. I believe you are asking to insert the IDs 1 and 2 into the Ticket table.
INSERT INTO Ticket ( ..., [Type Code], ... )I need to find out how this. I'm not too sure.
SELECT ..., (SELECT [Type Code] FROM Type WHERE type = 'Issue') AS [Type Code], ." Is this to verify whether the entries where correctly inserted?
In the last line you mentioned:
"Relational tables should intentionally be designed this way, with lookup tables rather than hard-coded values in tables. It may seem awkward to you at first, but this is the only proper way to design such tables."
I'd kindly like to ask what is the difference between hard coded values in tables and look up tables?. If I use the INSERT INTO function as you've demonstrated above to insert values into an empty table either from another table or values I specify would that be considered hard-coded or a look up table?.
Thanks in advance.
- Mr_X wrote:
Hi Scott. Thanks for your reply. I understood most of what you said. I'd kindly like to ask for some clarification on some parts. But here is what I understood from these steps (please correct me if I'm wrong):
(1) "INSERT INTO dbo.Type (type ) VALUES('Issue'), ('Research Task') You inserting two values into the 'type' column. I imagine that the 'type code' in this table is already generated as 1,2 respectively based on the line "type_code smallint NOT NULL IDENTITY(1, 1)".
For a ticket with a type of 'Issue', you would need to INSERT 1 into column Type Code on Ticket. I believe you are asking to insert the IDs 1 and 2 into the Ticket table.
(2) INSERT INTO Ticket ( ..., [Type Code], ... )I need to find out how this. I'm not too sure.
SELECT ..., (SELECT [Type Code] FROM Type WHERE type = 'Issue') AS [Type Code], ." Is this to verify whether the entries where correctly inserted?
In the last line you mentioned:
(3) "Relational tables should intentionally be designed this way, with lookup tables rather than hard-coded values in tables. It may seem awkward to you at first, but this is the only proper way to design such tables."
I'd kindly like to ask what is the difference between hard coded values in tables and look up tables?. If I use the INSERT INTO function as you've demonstrated above to insert values into an empty table either from another table or values I specify would that be considered hard-coded or a look up table?.
Thanks in advance.
(1) Correct. The IDENTITY property on the column means it will automatically generate a sequential number for the Type Code column.
(2) No. It's to retrieve the corresponding Type Code from its lookup table to INSERT into the main table. The developer/user will specify a type of "Issue", but you need to look that up and get the Type Code of 1 to insert instead. That's what the embedded SELECT does.
(3) I meant hard-coded in the main table, here the Ticket table. That is, where you INSERT 'Issue' or 'Research Task' directly into the Ticket table rather than a code. That is a poor method for at least the reasons I mentioned above (string is long, not 1 bytes, and the strings can be inconsistent from one row to another). Don't worry about the very minor "overhead" of using a lookup table -- SQL is optimized for just such lookup tables.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Thanks for your reply Scott. I played around with step 2 and did mange to get it to work, kind of. I was just wondering if I could make a custom column in the denormalized table with a code like:
UPDATE Correspondence
SET TypeCode = CASE [Type]
WHEN 'letter' THEN 1
WHEN 'email' THEN 2
WHEN 'User Note' THEN 3
WHEN 'Phone call' THEN 4
ELSE 5
END;
And then when I create the Normalized table just insert the values from the column above for example?. Thanks in advance.
Also, I finally shared a link to the table file. It's here: https://www.sqlservercentral.com/forums/topic/are-the-data-types-ive-chosen-the-best-choices#post-4051577
-
You can. That's the two choices for a code column: use a view to encode/decode and/or use a separate lookup table. A lookup table is technically a better design, but CASE statements can work.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Thanks for getting back to me. I'll settle for the CASE statement I just found it easier for me.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply