| Author |
 Topic  |
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
 Posted - 06/29/2007 : 12:12:17
|
barbie dolls, mashed potatoes, and dryer lint all in the same table. i love it!
elsasoft.org |
 |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 06/29/2007 : 12:17:23
|
"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"?  |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 06/29/2007 : 13:55:48
|
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 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 06/29/2007 : 15:10:57
|
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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/29/2007 : 15:16:15
|
or you can store your OTLT in an XML datatype with xml schema validation.
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 06/29/2007 : 16:04:18
|
Mladen, are you being serious? say goodbye to RI if you do that...
elsasoft.org |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 06/29/2007 : 16:54:51
|
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 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 06/29/2007 : 18:08:16
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 06/29/2007 : 22:02:09
|
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 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
Posted - 06/30/2007 : 00:17:59
|
yes. but joins, group bys, etc, are a real challenge!
elsasoft.org |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 06/30/2007 : 09:49:38
|
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 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 07/03/2007 : 05:10:19
|
Did Codd have a stammer then?
N-n-n-n-n-n-n-n-n-n-n-n-normal form? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 07/03/2007 : 09:52:00
|
saying "Smarter lets go" comes to mind...
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 07/12/2007 : 08:51:55
|
| Just curious but does sql server study dot com have anything to do with sql usa dot com? |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 07/12/2007 : 09:12:08
|
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 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 07/12/2007 : 10:06:59
|
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 |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
Posted - 07/12/2007 : 10:12:19
|
i respond to him/her when i'm amused by the question
_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Topic |
|
Twit List
