EXPERT RESPONSE
This design is the opposite, if you will,
of presenting one-to-many relational data in a single row (see
Consolidate data on multiple rows into one (01 August 2003).
In your case, you are storing it that way.
You have broken the law of
First Normal Form (check out the neat PowerPoint animation on that site).
Your penalty is that simple, easy, efficient SQL will not be available to you. Sorry.
But hey, maybe you didn't design it. Many people working with databases have to cope
with the status quo of unfortunate design decisions by some predecessor, or code that
would break if the design were changed, or management restrictions on where to allocate
redesign resources, or whatever. These people still need answers, and
"If this is your design, change it" won't cut it with them.
So. To find the rooms that have a particular item, you can use LIKE:
select Room
from ItemsByRoom
where RoomItems LIKE '%Table%'
The problem with this, of course, is that the database will probably
do a table scan, because an index on RoomItems will not be efficient.
As for getting the result set that you wanted, namely a count of how often each
item occurs in all rooms, you would be best to do that in your code. Return the Items
table by itself, save it into an array, return the ItemsByRooms table, and
then perform string-handling functions on the RoomItems field, updating a counter
in the matching array entry. This solution works, but very inefficiently.
Also, be aware that you have actually added to the problem; there is now yet
another piece of code that will break when the table is redesigned.
By the way, in Joe Celko's webcast
Advanced SQL tricks: Thinking in sets, Joe shows how to use SQL
to pull out all the terms in a comma-delimited list:
select I1.keycol
, cast( substring(','||instring||','
from S1.seq + 1
for S2.seq - S1.seq - 1) as integer )
from InputStrings as I1
, Sequence as S1
, Sequence as S2
where substring(','||instring||','
from S1.seq
for 1 ) = ','
and substring(','||instring||','
from S2.seq
for 1 ) = ','
and S1.seq < S2.seq
and S2.seq = ( select min(S3.seq)
from Sequence as S3
where S1.seq < S3.seq )
The seq column in the Sequence table contains integers from 1 through
one more than the maximum number of terms allowed. In the case of your items in a room,
you would not need to CAST as INTEGER because your terms are strings.
If the above query were declared as a view, let's say NormalizedRoomItems,
then your query for the count of how often each item occurs in all rooms would be:
select Items.Id
, Itemname
, count(*) as QtyUsed
from Items
left outer
join NormalizedRoomItems
on Items.Id = NormalizedRoomItems.Id
And, of course, it should be obvious that this is exactly the simple, easy, efficient
SQL that you would use if the table were designed in first normal form.
|
