Language, Identity and Self-Knowledge

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)

Language, Identity and Self-Knowledge

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-13 : 10:25:38

Actually, this post has nothing to do with "Self Knowledge". But I bet it caught the eye of a few readers.

I've been shameless about using IDENTITY keys for many / all of my course tables. For example:

CREATE TABLE dbo.Courses (
CourseID INT NOT NULL IDENTITY (1032,100) PRIMARY KEY , -- Identity of course
CourseName VARCHAR (100) UNIQUE NOT NULL , -- Title of course
)
GO

I need to expand the table structure to include course languages, which are reported on as the same course. The Coursename field will be in the characterset of the native language. Each invocation of the course in another language will reuiqre that the CourseID field have the same value, so a "Language" column (2 character: en, jp, de) will be needed to make the column pair unique for each course.

Now identity (on the CourseID column) will go out the window for this table, along with the primary key designation. How would I modify the CREATE TABLE to identify the 2 column pair as the unique key (clustered index?)

CREATE TABLE dbo.Courses (
CourseID INT NOT NULL , -- Identity of course
Language CHAR (2) NOT NULL ,
CourseName NVARCHAR (100) UNIQUE NOT NULL , -- Title of course
)
GO

CREATE CLUSTERED INDEX CourseIndex
ON Courses (CourseID, Language)
GO

Is this the optimal way to create uniquely indexed rows, and is there any shorthand to join the course table with another table?

FROM Courses C
INNER JOIN Modules M
ON C.CourseIndex = M.ModuleIndex

- Is there a shorthand syntax for a clustered index like above? or -

FROM Courses C --
INNER JOIN Modules M
ON C.CourseID = M.ModuleID AND C.Language = M.Language

An alternate solution would be to avoid the whole 2-column approach to language and tack on the language to the CourseID..

CREATE TABLE dbo.Courses (
CourseID VARCHAR (6) UNIQUE NOT NULL , -- Identity of course
CourseName NVARCHAR (100) UNIQUE NOT NULL , -- Title of course
)
GO

The format for CourseID could be CCCCLL where CCCC is the Course and YY is the language. With an approach like this, the LL can be masked to count all users who have completed a specific course accross all languages. Of course, the overhead of masking a column in a select isn't optimal, but it isn't a killer either.

I'd appreciate any thoughts about best practices / optimal methods to extend my DB to include multiple languages.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 11:59:44

Sam,

I'd go with the 2 column approach...it's a composite the way you would have if you were to concat the data anyway, plus it would be a derived column that you'd have to manage. Using the database you can inforce the rules...plus I imagine you would have a language code table with a foreign key...

I'm assuming here that each course can be in a different language. But if that's not the case, then you just make the language an attribute of the course key.

Does that help? Maybe you could clarify what your req is?

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-13 : 12:23:15

Hi Brett,

I'm in the middle of making a decision about how to best go forward with tracking and reporting multiple languages. I have one solution implented that dodges the issue completely by grouping multiple courses (en, de, jp) into a single report group. No modifications to the CourseID indexing structure are needed with this approach.

I am mulling over the consequences of modifying the table structure to define the grouping of languages into a single course. This will make the reporting simpler and more obvious, but has far-reaching consequences to the database and stored procedures.

I agree that adding another column would be the best approach. It's also a lot more work. Not to add the column, but to revise the SPs.

I would like someone to confirm if I've go the syntax for creating a clustered index on the language column correct. Second, I've no experience coding joins that use clustered indicies. Is anything special syntax necessary on the JOIN to indicate it's a 2 column index?

Sam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 12:32:13

Add a table of Languages, if you don't already have it.

Then create a table of CourseLanguages:

(primary key: CourseID, LanguageID)

CourseID
LanuageID
LanguageCourseName
... other course/language info, like dates or rooms or whatever ...

Don't store course info in this table. Don't store langugae info in this table. just info that relates to BOTH a course and a language together. (i.e., be sure it is normalized)

Keep all stuff related only to courses but NOT dependant on the language in the courses table. All stuff related only to lanuages but not courses, keep in the languages table.

Do not alter your courses table, other than to include a "default language" field or something like that (if you like).

When you want to know which courses are available in which languages, you just join to the CourseLanguages table as needed.

---

Multi-column joins are just done using an AND clause:

Select ...
FROM
Table1
INNER JOIN
Table2
ON
Table1.Field1 = Table2.Field1 AND
Table1.Field2 = Table2.Field2

- Jeff

Edited by - jsmith8858 on 05/13/2003 12:33:40

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 12:36:21

Well,

1. A table can have only 1 Clustered Index. That's the physical order the table is sorted by.
2. You don't mention primary keys as a constraint, not that you need one..
3. BOL has a wealth of info on the subject..a sample:


CREATE TABLE t1 (a int, b int, c AS a/b)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
GO
INSERT INTO t1 VALUES ('1', '0')
GO

4. No special need for Joins except when you provide an index hint. The optimizer (usually, not always) picks the best path to the data.
5. Changing the sprocs....would a view help. Use it to "fake out" the sprocs to minimize the impact of the change...unless of course it's a complete re-engineering..then you're stuck.

MOO

Brett

8-)

Edited by - x002548 on 05/13/2003 12:37:53

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-13 : 12:51:35

Thanks, I'll think about all of this.

I'm pleased I rated a moo out of Brett.

Is this a rating like one out of five?

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 13:16:41

MOO (My Own Opinion)

Got it from someone here at the site...don't know if it means the same thing or not...

It was his goal in his bio to say MOO as often as possible...

forget who it was...

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-13 : 13:33:47

I remember that profile and assumed it was yours, since you drop it in now and then. Didn't realize you had cast it into an acronym.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 14:09:07

Well I couldn;t find it...it most references have it as:

quote:

MOO MUD, Object Oriented [Internet]

Does that mean I have to stop MOO-ing???

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-13 : 21:27:48

quote:
Does that mean I have to stop MOO-ing???

You'd better not stop! We can't learn anything from you if you do!

However, I do disagree somewhat with this nugget:

quote:
2. You don't mention primary keys as a constraint, not that you need one..

Ummmmm, YES, you DO NEED A PRIMARY KEY on your tables. The fastest way to end up with a FUBAR database or data is to ignore basic fundamental database concepts like primary keys.

I'm not sure I see much advantage in creating a unique clustered index on the calculated column, vs. creating a primary key on the a and b columns. IIRC you can only index a calculated column in SQL Server 2000, and if either a or b is null that index will either fail (which is good) or generate a null and accept it (which is bad). A primary key would avoid this entirely.

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-14 : 09:39:56

Thanks rob

Except for situations where yuo need a relationship to other tables (and again I stress you don't need to, but then again why not)...

quote:

Ummmmm, YES, you DO NEED A PRIMARY KEY on your tables. The fastest way to end up with a FUBAR database or data is to ignore basic fundamental database concepts like primary keys.

Doesn't a clustered unique index do exactly the same thing as a constraint(PK)?

Except for the fact that it can't be used in RI?

Ok, now, the proverbial MOO

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-05-14 : 19:07:15

quote:
Doesn't a clustered unique index do exactly the same thing as a constraint(PK)?

Except for the fact that it can't be used in RI?

Actually, a unique CONSTRAINT is usable for RI, or you can use a primary key (technically, they are NOT the same thing, but in practice they are essentially identical...look in Books Online for more of the details) And neither unique indexes/constraints nor primary keys need to be clustered, and there are some instances where another clustering key is better (think of the phone book)

Although an index can be unique, and a unique constraint uses an index to perform its job, a constraint and index are still two different things and don't perform the same functions; that they happen to overlap in functionality in some cases doesn't change that. It's always better to use a hammer to hammer nails, rather than the handle of a screwdriver, even though they can both do the job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources