Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I have a table Room with primkey on column room_id and another table Items with primkey item_id. Each Item has a foreign integer key to room_id. When my program runs, it only looks at items in a single room so it would help if my primary key was on (room_id, item_id) in this order so that data is sorted on disk in this order.
Is this possible? Or do I need to duplicate room_id in Item and not have it as foreign key?
Your clustered index almost certainly should include RoomId for the reasons you correctly identify: it provides an efficient access path to all records for a given Room.
But there are other motivations are well. If Item is a child of Room, then you probably have an identifying relationship like Room contains zero to many items.
This would naturally lead your primary key to be something like (RoomId,<columns that determine a unique item>). So that could be (RoomId,ItemId)assuming that ItemId has some value beyond being a row pointer.
Otherwise it could be (RoomId,SerialNumber) or (RoomId,ItemType) - the exact answer depends on the data you're trying to store.
Do Items move around or stay in the same Room forever?
If they move around and [room_id] is the first key column on your clustered index then you're going to end up with a high amount of physical fragmentation and maintenance overhead.
I'd recomend just Clustering on item_id and using room_id on a nonclustered index, perhaps with covering columns to satisfy your application's needs.
How big are the rooms? are we talking end scene of Raiders of the Lost Ark? If the rooms don't hold a lot of items then a Key Lookup is Good. On small datasets a Nested Loop is the fastest Join.
I am assuming that item_id and room_id are not GUIDS. Otherwise that's going to need a longer answer.
