Hello all,

I got a chance to peak into a database system. Part of its design is
rather unfamiliar to me. When I look at the diagram generated by SQL
Server, there are many floating tables. Eventually it turns out that
these many floating tables are actually not floating. Their table
names relate to fields (as TableID) in other tables. In this case,
you can get a handle to one of such tables by search TableID columns
in other tables.

To be more specific, the database is a microarray database implemented
in SQL Server 2000. They have a table called MICROARRAYS. In this
table, there is a column called table_id. These table_ids are in fact
table names of a bunch of other tables.

My questions are

1) Is this good relational design?
2) How well is this kind of design supported in SQL Server?
3) Are there better alternatives?

Any other comment or link to helpful resources will also be
appreciated.

Thanks,

Eric Wu

ewu (neurite@excite.com) writes:
> I got a chance to peak into a database system. Part of its design is
> rather unfamiliar to me. When I look at the diagram generated by SQL
> Server, there are many floating tables. Eventually it turns out that
> these many floating tables are actually not floating. Their table
> names relate to fields (as TableID) in other tables. In this case,
> you can get a handle to one of such tables by search TableID columns
> in other tables.
>
> To be more specific, the database is a microarray database implemented
> in SQL Server 2000. They have a table called MICROARRAYS. In this
> table, there is a column called table_id. These table_ids are in fact
> table names of a bunch of other tables.

I'm not sure that I understand this. I'm not even sure that I want to
understand it.

It certainly does not seem like a regular use of a relational database
engine, and while it work with smaller amount of data, I doubt that
this system hosts 100 GB of data. Or even 1 GB.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

On 10 Sep 2003 10:14:56 -0700, neurite@excite.com (ewu) wrote:

>I got a chance to peak into a database system. Part of its design is
>rather unfamiliar to me. When I look at the diagram generated by SQL
>Server, there are many floating tables. Eventually it turns out that
>these many floating tables are actually not floating. Their table
>names relate to fields (as TableID) in other tables. In this case,
>you can get a handle to one of such tables by search TableID columns
>in other tables.
>
>To be more specific, the database is a microarray database implemented
>in SQL Server 2000. They have a table called MICROARRAYS. In this
>table, there is a column called table_id. These table_ids are in fact
>table names of a bunch of other tables.

I don't know what a microarray is. (A millionth of an array?)

>My questions are
>
>1) Is this good relational design?

Can't say, based only on what you've posted. The "floating" tables
might represent subtypes. If that's the case, then table names that
are stored in the supertype (I presume) table should be used by a
trigger or stored procedure as a part of "referential" integrity.
(You have to indicate the subtype table somehow, but using the table
name itself constitutes tight coupling.)

>2) How well is this kind of design supported in SQL Server?

If it's really a supertype/subtype issue, not as well as I'd like.
You really need deferred constraint checking to do it right.

>3) Are there better alternatives?

I'm not sure anyone here can answer without knowing *why* the database
was designed that way.

--
Mike Sherrill
Information Management Systems