Best Practices: multiple location tables?

[JP-ODU]

My organization (a university), has previously tried to enforce using a single location table, under the presumption that simpler/a single source of truth is better.

This has resulted in our location table essentially being a record of room numbers, however, this has begun to show conflicts:

1. "Simple building address" it's not possible to simply select a location that is a singular discrete building address, because we have locations organized by individual rooms, not a location that's just the building, itself

2. Non-classroom locations. Our network team, in particular, has raised the concern that they often have "relative locations:" for example "the utility closet opposite room 304" or "the crawlspace under room 207's seating."

It begins to seem to me that we might be better served with a Room Location table, a Building Location table, and a Network Device Location table--but is that in fact the best approach, or should I be trying to find a "one size fits all" approach?

How do other organizations handle best practices of their various locations?

[Willem]

Hi,

Things to consider:

• The Location table has a Parent field. Could a hierarchy work for you in this case?

A building location has multiple rooms and multiple device location. So Buildings are the parents and have child locations for the rooms.

• If you feel the data you record is different per type of 'location' then a new table could be an option. Try to limit the amount of custom tables if possible.

[Kieran Anson]

Hey,

It sounds like the functions of facilities management would have done nicely (particular the space management) aspect. However that product is now discontinued but you could potentially look into mimicking its behaviour and functions?

Space Management

Effectively this product had a class hierarchy of location, campus, building, floor, space, zone

Alternatively, rather than creating a custom table, how about adding a custom field such as "class" which you can define location types. e.g city, office, room, shed. This could allow for some data management where a shed can't be the parent of a city etc.