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