How do I represent groups of records when not every record is a member of a group?

I'm building an application to let col开发者_JAVA百科lege students search for housing. My model includes a Room table with the room dimensions, whether it's reserved for hall staff, and its room number, among other things. However, some rooms participate in "suites" of 2-8 rooms. How would I model that?

I was considering a suite_id column in the Room table, with NULL allowed to account for (the majority of) the rooms that aren't suites. It seems like there should be a more elegant way to do things, though.

As for what I want to do with the data, I want to be able to check if a room is part of a suite, and I want to be able to query for all suites (optionally limiting by size).

Any advice would be much appreciated!

That schema works fine.

To check if a room is part of a suite, check if the column is null or not in your application.

To query for all suites, select rows WHERE suite_id IS NOT NULL, and limit by size by GROUP BY suite_id HAVING COUNT(*) = number_of_rooms_you_want.

Though the syntax might be slightly different depending what RDBMS you're using. You didn't tag the question with any.

There is nothing inelegant about having a suite_id which is nullable. This basically says, in your data model, a room can be part of a suite. This is a direct representation of the situation and is therefore elegant.

The alternatives are much worse. Consider for a moment, if you will, what the model might look like if you separated rooms out in to different tables. One for suites, and one for non-suites. Suddenly your search criteria has to go over two tables. You replicate information over two tables (when in actuality, only a single identifier is the difference). This is NOT representative of the problem, and just causes more legwork, and the potential performance hit of two search queries (in a UNION, presumably) to get your results back. Oh, and if you add a common property, you need to add it in two places and do whatever maintenance work you have to do to recognize that new column, twice.

Another alternative would be to include all the fields that pertain to a "suite" in to the main table. However, this means every room that was not part of a suite had redundant data in it. As you've said, it's rare for them to be part of a suite so the majority of these records will have dead space in them. Once again, it is also not representative of the situation.

The solution you have come to is correct and robust.

If a room can possibly be a part of different suites, you might consider creating a separate table SuiteRooms (which might somewhat reflect the idea of how sweet they are).