Another DB design question:
-
If I had three tables (the 3T) called Building, Floor, and Room (the data is what you'd probably expect) and I wanted to be able to manage inactive periods (or permanent shut-downs) of any one record in any of those tables, what would be the best way to do it? My first thought (which doesn't seem elegant) was to create an inactiveId field in the 3T, referencing an Inactive table which would store the beginDate, endDate (if any), and inactivityReason. Now consider a floor is scheduled to undergo renovations from January 10th to February 10th and one of the rooms of that same floor is scheduled for a permanent installation of WiFi and AC outlets for every desk from January 2nd to January 29th. Should I store the fields buildingId, floorId, and roomId in the Inactive table or is the inactiveId in the 3T enough? Should I create a trigger (or a scheduled function) to remove any "expired" inactiveId from the 3T (i.e. the affected room would have a null inactiveId on January 30th and the affected floor would follow suit on February 11th)? If the above is completely wrongheaded, please let me know. I'm not a dba, just a lowly programmer. And, since I want to design it to be easy to use for future programmers, I don't need everything to be strictly normalized if there's a usability payoff for the end user (although my instincts are to usually go up to 3NF and 2NF).
Alex
-
If I had three tables (the 3T) called Building, Floor, and Room (the data is what you'd probably expect) and I wanted to be able to manage inactive periods (or permanent shut-downs) of any one record in any of those tables, what would be the best way to do it? My first thought (which doesn't seem elegant) was to create an inactiveId field in the 3T, referencing an Inactive table which would store the beginDate, endDate (if any), and inactivityReason. Now consider a floor is scheduled to undergo renovations from January 10th to February 10th and one of the rooms of that same floor is scheduled for a permanent installation of WiFi and AC outlets for every desk from January 2nd to January 29th. Should I store the fields buildingId, floorId, and roomId in the Inactive table or is the inactiveId in the 3T enough? Should I create a trigger (or a scheduled function) to remove any "expired" inactiveId from the 3T (i.e. the affected room would have a null inactiveId on January 30th and the affected floor would follow suit on February 11th)? If the above is completely wrongheaded, please let me know. I'm not a dba, just a lowly programmer. And, since I want to design it to be easy to use for future programmers, I don't need everything to be strictly normalized if there's a usability payoff for the end user (although my instincts are to usually go up to 3NF and 2NF).
Alex
A forth table with a table type, id field, inactive start, inactive end date, and reason code where the type and id are the primary key will give you a good starting point. It is better design to create three tables but if done right the one will serve you well.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway -
If I had three tables (the 3T) called Building, Floor, and Room (the data is what you'd probably expect) and I wanted to be able to manage inactive periods (or permanent shut-downs) of any one record in any of those tables, what would be the best way to do it? My first thought (which doesn't seem elegant) was to create an inactiveId field in the 3T, referencing an Inactive table which would store the beginDate, endDate (if any), and inactivityReason. Now consider a floor is scheduled to undergo renovations from January 10th to February 10th and one of the rooms of that same floor is scheduled for a permanent installation of WiFi and AC outlets for every desk from January 2nd to January 29th. Should I store the fields buildingId, floorId, and roomId in the Inactive table or is the inactiveId in the 3T enough? Should I create a trigger (or a scheduled function) to remove any "expired" inactiveId from the 3T (i.e. the affected room would have a null inactiveId on January 30th and the affected floor would follow suit on February 11th)? If the above is completely wrongheaded, please let me know. I'm not a dba, just a lowly programmer. And, since I want to design it to be easy to use for future programmers, I don't need everything to be strictly normalized if there's a usability payoff for the end user (although my instincts are to usually go up to 3NF and 2NF).
Alex
If this is the main aspect for the design of the database, I would create the 3T primary keys the same way I would if it was an accounting system. A character key with the first 3 digits designating building, next 3 floor and the last three room (altered to your specs of course). This way you could include ranges of any size for your shutdowns without having to create multiple records for each structure. It would come in very handy when developing a calandar of building projects. I usually try to avoid having any real meaning (besides row identifier) in my primary keys but, in this case it may make sense. Building 5: 005000000 Second Floor, blg 5: 005002000 Room 234, 2nd Fr, blg 5: 005002234 Inactive Table -------------- PrimaryKey StartStructureId EndStructureId StartDate EndDate Reason
-
If I had three tables (the 3T) called Building, Floor, and Room (the data is what you'd probably expect) and I wanted to be able to manage inactive periods (or permanent shut-downs) of any one record in any of those tables, what would be the best way to do it? My first thought (which doesn't seem elegant) was to create an inactiveId field in the 3T, referencing an Inactive table which would store the beginDate, endDate (if any), and inactivityReason. Now consider a floor is scheduled to undergo renovations from January 10th to February 10th and one of the rooms of that same floor is scheduled for a permanent installation of WiFi and AC outlets for every desk from January 2nd to January 29th. Should I store the fields buildingId, floorId, and roomId in the Inactive table or is the inactiveId in the 3T enough? Should I create a trigger (or a scheduled function) to remove any "expired" inactiveId from the 3T (i.e. the affected room would have a null inactiveId on January 30th and the affected floor would follow suit on February 11th)? If the above is completely wrongheaded, please let me know. I'm not a dba, just a lowly programmer. And, since I want to design it to be easy to use for future programmers, I don't need everything to be strictly normalized if there's a usability payoff for the end user (although my instincts are to usually go up to 3NF and 2NF).
Alex
I would create the additional table on Room with an ID and from to date. If you deactivate a floor then create a record for each room on the floor. Please, do not prefix your ID field with any intelligence - it is a fundamentally bad design. The reason for the room level table is to give you the most flexibility without inflicting a complex set of rules (around the level you want to disable). You could then create views that would count the number of disabled rooms and compare it to the floor, room count to identify a disabled floor/building.
Never underestimate the power of human stupidity RAH