Design question re: unique constraint

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

SQL Server 2000 Forums

SQL Server Development (2000)

Design question re: unique constraint

Author

Topic

dark_perfect
Starting Member

4 Posts

Posted - 2010-08-10 : 07:08:51

Hi,

If I were to have a Laptops table that looked like this:

CREATE TABLE Laptops(
	laptop_name VARCHAR(32) PRIMARY KEY,
	manufacturer VARCHAR(32),
	model VARCHAR(64),
	OS_id INT NOT NULL,
	
	FOREIGN KEY (OS_id) REFERENCES OperatingSystems(OS_id)
	ON DELETE CASCADE
	ON UPDATE CASCADE
)

And also an OperatingSystems table which looked like this:

CREATE TABLE OperatingSystems (
	OS_id INT IDENTITY(1,1) PRIMARY KEY,
	OS_name VARCHAR(256) UNIQUE
)

Is this a valid design? I'm specifically worried about the unique contrainst on OS_name. I added it because we have around 100 laptops, all of which have installed one of approx ten different operating systems. Therefore, when I add a laptop to the database, it could add its Operating System via a stored procedure, like this:


CREATE PROCEDURE AddOS @OS_name VARCHAR(256)
	AS	
	DECLARE @osID INT

	INSERT INTO [OperatingSystems] SELECT @OS_name WHERE NOT EXISTS (SELECT 1 FROM [OperatingSystems] WHERE OS_name = OS_name)
	
	IF (@@ROWCOUNT > 0)
		SET @osID = SCOPE_IDENTITY()
	ELSE
		SELECT @osID=OS_id FROM OperatingSystems WHERE OS_id = @osID

	RETURN @osID

The idea was, as you can probably guess, to reduce repeated data. Is there a better way of doing any of this? The stored procedure method of inserting, then retrieving the ID (possibly through a select) seems a bit longwinded to me?

Thanks, guys!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-08-10 : 11:39:11

What hit me was that your data elements were reallllllly long. Does anyone really have a laptop with a 32 character name? Why don't you identify the manufacturers by their stock ticker symbol (NYSE is 3; NASDEQ is 5, I think)or some other standard code? Things like "IBM", "AAPL" are easy to read, but it takes an effort to figure that Acer is "ASIYF"

Since there are not that many laptop OS around and don't change that often, they would fit into a short list in a CHECK() constraint. Here is a suggestion:

CREATE TABLE Laptops
(laptop_name VARCHAR(15) NOT NULL PRIMARY KEY,
laptop_manufacturer CHAR(5) NOT NULL,--stock ticker code
laptop_model VARCHAR(64), -- way too long!
os_name VARCHAR(15) NOT NULL
CHECK (os_name IN (<<~ 10 operating systems>>))
);

>> Is this a valid design? <<

Except for the use of IDENTITY as a key, yes. But it is more work than you need to do.

>> The idea was, as you can probably guess, to reduce repeated data. Is there a better way of doing any of this? <<

You did not reduce it; you changed it to an integer and added the need for a JOIN to get it back.

Mother Celko's Heuristic:
1) If the list of values is short and stable, use a CHECK( IN..)
2) If the list of values is long or non-stable, use REFERENCES
3) The terms short/long and stable/non-stable are relative and vague.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

dark_perfect
Starting Member

4 Posts

Posted - 2010-08-13 : 06:49:12

Hi,

Thanks for the reply!

quote:
What hit me was that your data elements were reallllllly long. Does anyone really have a laptop with a 32 character name?

32 characters is perhaps a bit too long for laptop names - I always tend to go for longer elements to ensure that nothing too long is inserted and we end up losing data! This may be something I can reconsider though.

quote:
Why don't you identify the manufacturers by their stock ticker symbol

In this case, the data that will be inserted in the column is extracted from a DxDiag XML file, in which the manufacturer is similar to "Acer, Inc." I don't really have any control over what comes out of DxDiag which is why i can't really use stock ticker symbols. Though, again, I suppose your point about the data elements being too long is still valid in this case!

quote:
Since there are not that many laptop OS around and don't change that often, they would fit into a short list in a CHECK() constraint.

That's a great tip which I could use in future (I have been wondering how to use an 'enumeration', for which this could be a valid replacement), but again, the values to check for are, at present, unknown to me. This is because the values will be extracted from a DxDiag file, and therefore resemble strings such as "Windows XP Professional (5.1, Build 2600) Service Pack 3 (2600.xpsp_sp3_gdr.aaabbb-cccc)". If it were simply "Windows XP", then I'm sure the check constraint would be of great use to me!

quote:
Except for the use of IDENTITY as a key, yes

Ok, thanks. I think this probably stems from my own misunderstanding of how primary keys and identities work (or how they work together). I'll read up on it.

quote:
You did not reduce it; you changed it to an integer and added the need for a JOIN to get it back.

The reason I did this is because the strings, whilst (hopefully) few, are fairly long, as previously mentioned, because DxDiag is outputting long strings. I thought it was better to use references to a table with a unique constraint on the string, instead of inserting the string straight in, which could use 100 characters per row (unnecessarily). This is what I meant by reducing repeating data, though I realise that just replacing data with an integer isn't reducing it - should have worded that differently! I think this, therefore, falls in line with your heuristic (specifically, number 2, as the list of values is non-stable)?

Thanks for the feedback - feel free to comment on or amend anything i've mentioned.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources