Last post Jul 24, 2017 08:09 AM by Edward Z
Jul 21, 2017 07:03 AM|f.a.rodriguez|LINK
I am developing a RESTful web api, which concerns itself with estate agents doing inspections of houses. So we have entities such as properties, which contain rooms which contain items. For each of these, one can specify the condition of the room/item, make
notes, take pictures, specify what needs to be fixed and by whom and when, etc. In my database (MySQL) I created a `properties` table, a `rooms` table and an `items` table. The `rooms` and `items` table are lookup table with all possible types of rooms (bedroom,
bathroom, kitchen, etc) and items (bed, light, carpet, etc). I then have junction tables to link items to rooms and rooms to properties. So I have two tables called, called `propertyrooms` and `roomitems`. The `propertyrooms` table has a `PropertyId` and a
`RoomId`, as well as room_condition, notes, photos, etc. I have also, maybe foolishly, included an auto-incrementing ID primary key column. I've done exactly the same for `roomitems`.
I realized when creating the web api (ASP .Net Core 1.1) that I had to decide how to structure the routing. For example, to get all properties, I have:
and a specific property:
to get all rooms in a property, I have:
but the tricky part I am facing now is to get a specific room. I could either have:
In the two lines above, both endpoints refer to the same room. In the first endpoint I am identifying the room by a composite key of PropertyId = 5 and AreaId = 7, and in the second I am using the `propertyrooms` table ID
primary key field. I guess I don't need a primary key in the `propertyrooms` and `roomitems` tables - I can just have the two composite keys. Is there perhaps a best practice regarding this, either from a database perspective or from a RESTful perspective?
Should I keep both the composite key, and the auto-incrementing primary key, so that the client consuming the API has the option of calling a room using either method? I'd love to hear any thoughts on this... Thank you in advance...
Jul 21, 2017 07:51 AM|Kelmen|LINK
from db perspective, it is always best to have 1 primary key. as business logic can change over time.
today u using 2 keys as primary composite keys, 6 months down the road, the logic become complex, new field need to become the 3rd primary composite key.
if u adopting single primary key approach, u just need to create an unique index over the "composite keys" for control purpose, and it is easy to be drop and create new set as needed, with far much less much impact to the system logic or design.
Jul 21, 2017 07:54 AM|f.a.rodriguez|LINK
That's a very good point Kelmen, thank you. Although in this case I don't forsee the structure of `properties`, `rooms`, and their relationship changing in any significant way, one can never be sure...
EDIT: Also, I just realized, I do need a primary key anyway, because one room could be assigned to a property multiple times (e.g. a property could have 3 bedrooms and 2 bathrooms) so I need a way to be able to identify a particular bedroom
(e.g. master bedroom or guest bedroom, etc.)
Jul 21, 2017 08:06 AM|f.a.rodriguez|LINK
I just realized something....
makes no sense, because that is referring to a room of type 7 (bedroom) in property with ID = 5. But that property could have 2 or 3 or 4 bedrooms.... So I'd actually have to do
which probably makes more sense...
Jul 24, 2017 08:09 AM|Edward Z|LINK
>>which probably makes more sense
In my option, it depends on your business logic.
I often use this route when operate propertyrooms with Get by Id, which means this is specific URL to propertyrooms controller.
>> GET: /api/properties/5/propertyrooms/23
I often use this route when I need specific propertyroom which is under specific property which means this is a specific URL to properties controller.
Even though they may return the same result, the route should be more understandable, and we could know the function from the url.