Architecture question -- grouping different kinds of stuff...

[turnstyle]

Hi all,

I'm hoping somebody here might be able to point me in the right
direction.

Basically, I'm looking for a "good" way to build tables such that
different kinds of stuff can grouped.

For example, let's say I have "rooms" and they can contain "people"
and/or "books" and/or "furniture" -- each of those objects (rooms,
people, books, furniture, etc) may have their own attributes -- but the
idea is that a room may contain 0 or more of each of the others.

I'll need to do lots of selects to retrieve the contents of specific
rooms.

Is there a standard / good way to go about this sort of thing?

Somehow giving people, books, furniture a room_ID (foreign key) seems a
bit clumbsy -- ie, do I have to run a select for each?

thanks kindly, -Scott

[turnstyle]

Re: Architecture question -- grouping different kinds of stuff...

Following up on my own question above, let's say I have these tables:

Rooms
.. . RoomID

People
.. . PersonID
.. . Name

Books
.. . BookID
.. . Title

Furniture
.. . FurnitureID
.. . FurnitureType

I could group stuff into rooms with something like:

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID

(where ContentType could be person, book, furniture, etc)

But it seems like if there were 20 different kinds of things, I'd have
to run a select for each.

I'm hoping this is a standard SQL architecture problem, but I lack the
vocabulary to know what to search for.

thanks, -Scott

[Erland Sommarskog]

Re: Architecture question -- grouping different kinds of stuff...

turnstyle (scott@turnstyl e.com) writes:[color=blue]
> Following up on my own question above, let's say I have these tables:
>
> Rooms
> . . RoomID
>
> People
> . . PersonID
> . . Name
>
> Books
> . . BookID
> . . Title
>
> Furniture
> . . FurnitureID
> . . FurnitureType
>
> I could group stuff into rooms with something like:
>
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
>
> (where ContentType could be person, book, furniture, etc)
>
> But it seems like if there were 20 different kinds of things, I'd have
> to run a select for each.[/color]

I would look into gathering all these contents into one supertable.
If then there are specicic attributes for person, books etc you can
can have sub tables for this.

This contents table would have a type field specifying the type of object.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[turnstyle]

Re: Architecture question -- grouping different kinds of stuff...

Hi Erland, thanks -- are you suggesting something like:

Rooms
.. . RoomID

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . PersonName
.. . BookTitle
.. . FurnitureType

or even

RoomContents
.. . RoomID
.. . ContentType
.. . ContentID
.. . VariableText1
.. . VariableText2

(where 'VariableText' would store different kinds of stuff, depending
on the ContentType)


Since some of these objects may have a few attributes, such a
supertable could have lots of nulls -- is that "ok"?

Thanks again for your help, -Scott

[Erland Sommarskog]

Re: Architecture question -- grouping different kinds of stuff...

turnstyle (scott@turnstyl e.com) writes:[color=blue]
> Hi Erland, thanks -- are you suggesting something like:
>
> Rooms
> . . RoomID
>
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . PersonName
> . . BookTitle
> . . FurnitureType
>
> or even
>
> RoomContents
> . . RoomID
> . . ContentType
> . . ContentID
> . . VariableText1
> . . VariableText2
>
> (where 'VariableText' would store different kinds of stuff, depending
> on the ContentType)
>
>
> Since some of these objects may have a few attributes, such a
> supertable could have lots of nulls -- is that "ok"?[/color]

Nah, rather I was thinking you would keep the existing tables, and
just add this RoomContents table. Probably you should move some columns
into this table, for instance a string that represents a name of some
sort. (That is "sofa" for furnitures.)

This would make it easier to write a simple query that lists all the
contents in the room. If you need furniture-specific information you would
go to that table.

In the end it may be a trade-off where you place things. If some attributes
are common to most contents, it may be better to have it as a nullable
column in the main table.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[turnstyle]

Re: Architecture question -- grouping different kinds of stuff...

The trouble is that each time I "load a room" I'll need at least a few
of the attributes of the things in that room.

So, if I understand correctly, I would keep all of those various
objects' attributes in one "supertable ," and perhaps keep some
additional attributes (those not needed when the room loads) in the
extra object-specific tables.

Does that sound about right?

Thanks again, -Scott

[Erland Sommarskog]

Re: Architecture question -- grouping different kinds of stuff...

turnstyle (scott@turnstyl e.com) writes:[color=blue]
> The trouble is that each time I "load a room" I'll need at least a few
> of the attributes of the things in that room.
>
> So, if I understand correctly, I would keep all of those various
> objects' attributes in one "supertable ," and perhaps keep some
> additional attributes (those not needed when the room loads) in the
> extra object-specific tables.
>
> Does that sound about right?[/color]

Yes, that was my thought.

In an object-oriented language, this is a little easier since you would
have "Contents" a base class (possibly virtual) and then have Furtinure
etc to inherit from that group. In SQL you cannot do this very well.
You could have a view that unites all tables, and then have an INSTEAD
OF trigger on the view, so when you inserts into the view, things fall
down in their respective places, although I am not sure that this would
be worth the effort.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp