SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 The Yak Corral
 Twit List
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 88

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 06/29/2007 :  12:12:17  Show Profile  Visit jezemine's Homepage  Reply with Quote
barbie dolls, mashed potatoes, and dryer lint all in the same table. i love it!


elsasoft.org
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 06/29/2007 :  12:17:23  Show Profile  Reply with Quote
"Two One True Lookup Tables?"

Yeah. You probably remember Screaming Lord Sutch's Monster Raving Loony Party complaint:

"Why is there only one Monopolies and Mergers Committee"?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/29/2007 :  13:55:48  Show Profile  Reply with Quote
I advocate implementing the One True Lookup Table as a partitioned view composed of queries against underlying TNF tables.

That way, you can take advantage of the worst features of both approaches.



If you really need OTLT, how about this approach? Create a type table (T_OTLT_TYPE) that defines the datatype of the OTLT values in T_OTLT, store the values in a sql_variant data type, and then use a trigger to ensure that OTLT_VALUE conforms to the data type in T_OTLT_TYPE? Then you could at least have rudimentary domain integrity.

Of course, at that point, you've duplicated the functionality of the SQL Server system tables...


create table T_OTLT
(
OTLT_ID			int		not null
	identity(1,1) primary key clustered ,
OTLT_TYPE_ID		int		not null,
OTLT_VALUE		sql_variant	not null
)
GO
-- Defines data type for values in OTLT_VALUE
create table T_OTLT_TYPE
(
OTLT_TYPE_ID		int		not null
	identity(1,1) primary key clustered,
[BaseType]		sysname		not null,
[Precision]		int		not null,
[Scale]			int		not null,
[Collation]		sysname		not null,
[MaxLength]		int		not null
)
GO
alter table [dbo].[T_OTLT]  with check 
add  constraint [FK_T_OTLT_T_OTLT_TYPE]
foreign key	([OTLT_TYPE_ID])
references [dbo].[T_OTLT_TYPE] ([OTLT_TYPE_ID])


CODO ERGO SUM

Edited by - Michael Valentine Jones on 06/29/2007 14:33:42
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 06/29/2007 :  15:10:57  Show Profile  Visit jezemine's Homepage  Reply with Quote
quote:
Originally posted by Michael Valentine Jones
Of course, at that point, you've duplicated the functionality of the SQL Server system tables...



indeed.

designing a type system that has integrity is nontrivial, and best left to the folks that actually work on the storage engine, imo


elsasoft.org
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 06/29/2007 :  15:16:15  Show Profile  Visit spirit1's Homepage  Reply with Quote
or you can store your OTLT in an XML datatype with xml schema validation.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 06/29/2007 :  16:04:18  Show Profile  Visit jezemine's Homepage  Reply with Quote
Mladen, are you being serious? say goodbye to RI if you do that...


elsasoft.org
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/29/2007 :  16:54:51  Show Profile  Reply with Quote
quote:
Originally posted by jezemine

Mladen, are you being serious? say goodbye to RI if you do that...


elsasoft.org



If you are using OTLT, havn't you alredy said goodbye to RI?

CODO ERGO SUM
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 06/29/2007 :  18:08:16  Show Profile  Visit jezemine's Homepage  Reply with Quote
yes, but it's even worse with xml I think.

with xml, you could make your database out of a single table with one column, and only one row, so it seems a more extreme violation.


elsasoft.org
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/29/2007 :  22:02:09  Show Profile  Reply with Quote
quote:
Originally posted by jezemine
...make your database out of a single table with one column, and only one row...


Sounds like the ultimate in flexibility!



CODO ERGO SUM
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 06/30/2007 :  00:17:59  Show Profile  Visit jezemine's Homepage  Reply with Quote
yes. but joins, group bys, etc, are a real challenge!


elsasoft.org
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 06/30/2007 :  09:49:38  Show Profile  Visit spirit1's Homepage  Reply with Quote
i always say:
if you're going to screw it up, screw it up real good!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/02/2007 :  13:35:59  Show Profile  Reply with Quote
Well isn't that normal form to the nth degree...joins be damned



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 07/03/2007 :  05:10:19  Show Profile  Reply with Quote
Did Codd have a stammer then?

N-n-n-n-n-n-n-n-n-n-n-n-normal form?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/03/2007 :  09:44:17  Show Profile  Reply with Quote
quote:
Originally posted by Peso

Look at this (3 pages), especially the answer at posted 6/22/2007 7:23:00 PM.

Background is found here
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=370393&p=1 (38 pages)
and here
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=373178 (16 pages)

Yes, I know some of you will haunt me with this topic


Peter Larsson
Helsingborg, Sweden



I see you're still going at it with Sergiy:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=373178&p=32




CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/03/2007 :  09:52:00  Show Profile  Visit spirit1's Homepage  Reply with Quote
saying "Smarter lets go" comes to mind...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 07/12/2007 :  08:51:55  Show Profile  Reply with Quote
Just curious but does sql server study dot com have anything to do with sql usa dot com?
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/12/2007 :  09:12:08  Show Profile  Visit spirit1's Homepage  Reply with Quote
yes... well we don't know based on the site
but it seems that the funketekun is a relative of the sql USA trainer.
at least we got to that conclusion in anther thread...
or was it this one?


_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 07/12/2007 :  10:06:59  Show Profile  Reply with Quote
I don't understand why anyone responds to funketekun. It's just too much frustration and a complete waste of time.

He never understands anything, he never figures anything out, and he just comes back on a new thread with the same problem a week later.




CODO ERGO SUM
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 07/12/2007 :  10:12:19  Show Profile  Visit spirit1's Homepage  Reply with Quote
i respond to him/her when i'm amused by the question

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/12/2007 :  10:39:54  Show Profile  Reply with Quote
Thanks for the useless advice, but I don't have any too offer

http://www.dbforums.com/showthread.php?t=1620053



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page
Page: of 88 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000