November 29, 2011 at 1:23 pm
I have 4 types of entities Buildings, Floors, Rooms, and Computers. The Computer table will store attributes about the computer along with it's location. I’m looking for some constructive criticism on these approaches so I get a better idea about modeling correct relationships between entities. I’ve read many articles about the pros and cons regarding natural vs. surrogate so I’m not wanting to get into that becuase I'm just doing this for a beter understanding, but I am looking for opinions regarding how my entities relate to each other and if they make sense.
Natural Key Model 1:
I’d say this approach to modeling says that a computer relates to one Building, one Floor, and one Room. However, Building, Floor, and Room are not directly modeled to relate to each other. To me this looks like a star schema design for BI with the Building, Floor, Room being dimensions and the Computer as a fact table.
CREATE TABLE [Building]
(
BuildingName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [Floor]
(
FloorName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [Room]
(
RoomName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [Computer]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
BuildingName VARCHAR(20) NOT NULL,
FloorName VARCHAR(20) NOT NULL,
RoomName VARCHAR(20) NOT NULL
)
ALTER TABLE dbo.Computer ADD FOREIGN KEY (BuildingName) REFERENCES dbo.Building (BuildingName)
ALTER TABLE dbo.Computer ADD FOREIGN KEY (FloorName) REFERENCES dbo.Floor (FloorName)
ALTER TABLE dbo.Computer ADD FOREIGN KEY (RoomName) REFERENCES dbo.Room (RoomName)
See attached image DatabaseDiagramNatualOption1.png for the diagram…
Natural Key Model 2:
I’d say this approach to modeling says that a computer relates to one “Location” meaning a combination of Building, Floor, and Room. The Building, Floor, and Room tables are directly related to each other becuase a Building has many Floors, a Floor has many Rooms, but a Computer has only one "Location".
CREATE TABLE [Building]
(
BuildingName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [Floor]
(
FloorName VARCHAR(20) NOT NULL ,
BuildingName VARCHAR(20) NOT NULL
CONSTRAINT [PK_Floor] PRIMARY KEY CLUSTERED
(
[FloorName] ASC,
[BuildingName] ASC
))
CREATE TABLE [Room]
(
RoomName VARCHAR(20) NOT NULL ,
FloorName varchar(20) NOT NULL ,
BuildingName varchar(20) NOT NULL
CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED
(
[RoomName] ASC,
[FloorName] ASC,
[BuildingName] ASC
))
CREATE TABLE [Computer]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
BuildingName VARCHAR(20) NOT NULL,
FloorName VARCHAR(20) NOT NULL,
RoomName VARCHAR(20) NOT NULL
)
ALTER TABLE dbo.Floor ADD FOREIGN KEY (BuildingName) REFERENCES dbo.Building (BuildingName)
ALTER TABLE dbo.Room ADD FOREIGN KEY (FloorName, BuildingName) REFERENCES dbo.Floor (FloorName, BuildingName)
ALTER TABLE dbo.Computer ADD FOREIGN KEY (RoomName,FloorName,BuildingName) REFERENCES dbo.Room (RoomName,FloorName,BuildingName)
See attached image DatabaseDiagramNatualOption2.png for the diagram…
Natural Key Model 3:
Other ways to model this data that I didn’t toss out??
November 29, 2011 at 1:54 pm
I'd personally approach it via the second method, at least for the primary build, but I wouldn't associate room to floor and building, I'd merely associate it to a floor and let the floor tell me which building it belonged to.
It's a pretty standard hierarchal model from my perspective. Computers >- Room >- Floor >- Building
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2011 at 1:56 pm
Option 2 is by far a better model than option 1 but it still is not quite right. You still have redundant data.
Building in the base. Everything comes from there. Then a floor belongs to a building. All buildings have 1 or more and all floors belong to a single building. Then you have rooms. Each room belongs to floor (it does not belong to a building). Each room belongs to a single floor and each floor has 1 or more rooms. Then you have computer. Each computer can be in only 1 room and each room can have 1 or more computers.
You had it very close but you don't want to have to edit Computers every time some other data changes. In other words putting the building in the computer table is redundant. You say what room the computer is in. From the room you can determine what floor it is on and in which building.
I tossed together a quick example of what I think is a better approach to this type of thing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 1:57 pm
I would model the three location entities with dependant relationships similar to Example #2.
The top two entities are Buildings and Computers as both are entry points to the structure.
Buildings must first exist in order to have floors. Floors must then exist before rooms do.
Likewise, Computers exist before being assigned to a given room and are not dependent upon the existence of a building.
It is important to note that not all rooms may have a computer.
CREATE TABLE [Building]
(
BuildingName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [Floor]
(
BuildingName VARCHAR(20) NOT NULL,
FloorName VARCHAR(20) NOT NULL
CONSTRAINT [PK_Floor] PRIMARY KEY CLUSTERED
(
[BuildingName] ASC,
[FloorName] ASC
))
CREATE TABLE [Room]
(
BuildingName varchar(20) NOT NULL,
FloorName varchar(20) NOT NULL ,
RoomName VARCHAR(20) NOT NULL ,
CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED
(
[BuildingName] ASC,
[FloorName] ASC,
[RoomName] ASC
))
CREATE TABLE [Computer]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
BuildingName VARCHAR(20) NULL,
FloorName VARCHAR(20) NULL,
RoomName VARCHAR(20) NULL
)
ALTER TABLE dbo.Floor ADD FOREIGN KEY (BuildingName) REFERENCES dbo.Building (BuildingName)
ALTER TABLE dbo.Room ADD FOREIGN KEY (BuildingName, FloorName) REFERENCES dbo.Floor (BuildingName, FloorName)
ALTER TABLE dbo.Computer ADD FOREIGN KEY (BuildingName,FloorName,RoomName) REFERENCES dbo.Room (BuildingName, RoomName, FloorName)
November 29, 2011 at 2:09 pm
Evil Kraig F (11/29/2011)
I'd personally approach it via the second method, at least for the primary build, but I wouldn't associate room to floor and building, I'd merely associate it to a floor and let the floor tell me which building it belonged to.It's a pretty standard hierarchal model from my perspective. Computers >- Room >- Floor >- Building
Ah, this makes sense..
November 29, 2011 at 2:16 pm
Sean Lange (11/29/2011)
Option 2 is by far a better model than option 1 but it still is not quite right. You still have redundant data.Building in the base. Everything comes from there. Then a floor belongs to a building. All buildings have 1 or more and all floors belong to a single building. Then you have rooms. Each room belongs to floor (it does not belong to a building). Each room belongs to a single floor and each floor has 1 or more rooms. Then you have computer. Each computer can be in only 1 room and each room can have 1 or more computers.
You had it very close but you don't want to have to edit Computers every time some other data changes. In other words putting the building in the computer table is redundant. You say what room the computer is in. From the room you can determine what floor it is on and in which building.
I tossed together a quick example of what I think is a better approach to this type of thing.
Great example, thank you! I shouldn't have used composite keys in this example, which is why I ended up with so many deep layers going down the relationship tree..
November 29, 2011 at 2:20 pm
michael.french 172 (11/29/2011)
I would model the three location entities with dependant relationships similar to Example #2.The top two entities are Buildings and Computers as both are entry points to the structure.
Buildings must first exist in order to have floors. Floors must then exist before rooms do.
Likewise, Computers exist before being assigned to a given room and are not dependent upon the existence of a building.
It is important to note that not all rooms may have a computer.
CREATE TABLE [Building]
(
BuildingName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [Floor]
(
BuildingName VARCHAR(20) NOT NULL,
FloorName VARCHAR(20) NOT NULL
CONSTRAINT [PK_Floor] PRIMARY KEY CLUSTERED
(
[BuildingName] ASC,
[FloorName] ASC
))
CREATE TABLE [Room]
(
BuildingName varchar(20) NOT NULL,
FloorName varchar(20) NOT NULL ,
RoomName VARCHAR(20) NOT NULL ,
CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED
(
[BuildingName] ASC,
[FloorName] ASC,
[RoomName] ASC
))
CREATE TABLE [Computer]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
BuildingName VARCHAR(20) NULL,
FloorName VARCHAR(20) NULL,
RoomName VARCHAR(20) NULL
)
ALTER TABLE dbo.Floor ADD FOREIGN KEY (BuildingName) REFERENCES dbo.Building (BuildingName)
ALTER TABLE dbo.Room ADD FOREIGN KEY (BuildingName, FloorName) REFERENCES dbo.Floor (BuildingName, FloorName)
ALTER TABLE dbo.Computer ADD FOREIGN KEY (BuildingName,FloorName,RoomName) REFERENCES dbo.Room (BuildingName, RoomName, FloorName)
How would you tie Computer to a location? If I understand your post it would look like this?
Building Computer
Floor
Room
November 29, 2011 at 2:27 pm
I think you're over-thinking it SQL-Dude.
Think more like this:
CREATE TABLE Bldg (BldgName VARCHAR(20) NOT NULL CONSTRAINT PK_Bldg PRIMARY KEY CLUSTERED)
-- Floor is a SQL Function and keyword
CREATE TABLE Floors
(FloorName VARCHAR(20) NOT NULL CONSTRAINT PK_Floors PRIMARY KEY CLUSTERED,
BldgName VARCHAR(20) NOT NULL CONSTRAINT FK_Floors_Bldg REFERENCES Bldg (BldgName)
)
CREATE TABLE Rooms
(RoomName VARCHAR(20) NOT NULL CONSTRAINT PK_Rooms PRIMARY KEY CLUSTERED,
FloorName VARCHAR(20) NOT NULL CONSTRAINT FK_Rooms_Floors REFERENCES Floors (FloorName)
)
CREATE TABLE Computers
(ComputerName VARCHAR(20) NOT NULL CONSTRAINT PK_Computers PRIMARY KEY CLUSTERED,
RoomName VARCHAR(20) NOT NULL CONSTRAINT FK_Computers_Rooms REFERENCES Rooms (RoomName)
)
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 29, 2011 at 2:37 pm
I did make a mistake in the order of the FK from Computer to Room. It should read like this:
ALTER TABLE dbo.Computer ADD FOREIGN KEY (BuildingName, FloorName, RoomName) REFERENCES dbo.Room (BuildingName, FloorName, RoomName)
The Primary Key of Room is a composite key of all three starting with Building, then Floor, and then lastly Room. The Foreign Key on Computer is to Room in the same order. Even though the attributes on Computer allow NULL, once a Computer is assigned to Room it has to be a valid Room.
To model this fully, I would actually add one more table and change the Computer table.
CREATE TABLE [Computer]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [ComputerRoom]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
BuildingName VARCHAR(20) NOT NULL,
FloorName VARCHAR(20) NOT NULL,
RoomName VARCHAR(20) NOT NULL
)
ALTER TABLE dbo.ComputerRoom ADD FOREIGN KEY (ComputerName) REFERENCES dbo.Computer (ComputerName)
ALTER TABLE dbo.ComputerRoom ADD FOREIGN KEY (BuildingName, FloorName, RoomName) REFERENCES dbo.Room (BuildingName, FloorName, RoomName)
November 29, 2011 at 2:56 pm
michael.french 172 (11/29/2011)
I did make a mistake in the order of the FK from Computer to Room. It should read like this:ALTER TABLE dbo.Computer ADD FOREIGN KEY (BuildingName, FloorName, RoomName) REFERENCES dbo.Room (BuildingName, FloorName, RoomName)
The Primary Key of Room is a composite key of all three starting with Building, then Floor, and then lastly Room. The Foreign Key on Computer is to Room in the same order. Even though the attributes on Computer allow NULL, once a Computer is assigned to Room it has to be a valid Room.
To model this fully, I would actually add one more table and change the Computer table.
CREATE TABLE [Computer]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [ComputerRoom]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
BuildingName VARCHAR(20) NOT NULL,
FloorName VARCHAR(20) NOT NULL,
RoomName VARCHAR(20) NOT NULL
)
ALTER TABLE dbo.ComputerRoom ADD FOREIGN KEY (ComputerName) REFERENCES dbo.Computer (ComputerName)
ALTER TABLE dbo.ComputerRoom ADD FOREIGN KEY (BuildingName, FloorName, RoomName) REFERENCES dbo.Room (BuildingName, FloorName, RoomName)
You can really yourself in trouble with that kind of data structure. You require in the ComputerRoom table a valid Building, Floor and Room. You do now however do any kind of checks to ensure the data is valid. That is why you make this is a hierarchy. Otherwise you have computer that belongs in Room 435, Floor 90, and Building 33. However Building 33 does not have floor 90, that is from building 22 and room 435 belongs to floor 4 in building 8. See where this is going. If the computer belongs to a room, the room belongs to a floor and the floor belongs to a building you don't have to validate all that. Plus you get the advantage of less data redundancy.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 29, 2011 at 3:57 pm
Evil Kraig F (11/29/2011)
I think you're over-thinking it SQL-Dude.Think more like this:
CREATE TABLE Bldg (BldgName VARCHAR(20) NOT NULL CONSTRAINT PK_Bldg PRIMARY KEY CLUSTERED)
-- Floor is a SQL Function and keyword
CREATE TABLE Floors
(FloorName VARCHAR(20) NOT NULL CONSTRAINT PK_Floors PRIMARY KEY CLUSTERED,
BldgName VARCHAR(20) NOT NULL CONSTRAINT FK_Floors_Bldg REFERENCES Bldg (BldgName)
)
CREATE TABLE Rooms
(RoomName VARCHAR(20) NOT NULL CONSTRAINT PK_Rooms PRIMARY KEY CLUSTERED,
FloorName VARCHAR(20) NOT NULL CONSTRAINT FK_Rooms_Floors REFERENCES Floors (FloorName)
)
CREATE TABLE Computers
(ComputerName VARCHAR(20) NOT NULL CONSTRAINT PK_Computers PRIMARY KEY CLUSTERED,
RoomName VARCHAR(20) NOT NULL CONSTRAINT FK_Computers_Rooms REFERENCES Rooms (RoomName)
)
I probably am.. But, it's good because I was trying to model this using natural keys. It's just not panning out to be easy to accomplish because take the example you have above I couldn't have two 1st Floors in the table becuase FloorName is the Primary Key value. If that was a surrogate key it would just be an integer and I'd have a unique composite index on FloorName, BuildingName columns. If I make FloorName, and BuildingName the composite Primary Key that works, but then I have to reference them all the way down the relationship structure...
November 29, 2011 at 4:08 pm
SQL Dude-467553 (11/29/2011)
I probably am.. But, it's good because I was trying to model this using natural keys. It's just not panning out to be easy to accomplish because take the example you have above I couldn't have two 1st Floors in the table becuase FloorName is the Primary Key value.
Very true, but you have to decide if a floor is an entity or a description lookup.
You've mixed meanings here. An entity is a discreet object. It cannot apply to two concepts simultaneously. A description can, and is associated differently. Let's look at this another way.
What's an entity with this type of description? Room 101 can be repeated in the Rooms table if you use a standard naming convention, even though there are Room 101s in all the buildings.
So, you have to decide if you're creating entities or descriptors.
If you're creating descriptors, you build it like this:
CREATE TABLE Bldg (BldgName VARCHAR(20) NOT NULL CONSTRAINT PK_Bldg PRIMARY KEY CLUSTERED)
-- Floor is a SQL Function and keyword
CREATE TABLE Floors
(FloorName VARCHAR(20) NOT NULL CONSTRAINT PK_Floors PRIMARY KEY CLUSTERED
)
CREATE TABLE Rooms
(RoomName VARCHAR(20) NOT NULL CONSTRAINT PK_Rooms PRIMARY KEY CLUSTERED
)
CREATE TABLE Computers
(ComputerName VARCHAR(20) NOT NULL CONSTRAINT PK_Computers PRIMARY KEY CLUSTERED,
RoomName VARCHAR(20) NOT NULL CONSTRAINT FK_Computers_Rooms REFERENCES Rooms (RoomName),
FloorName VARCHAR(20) NOT NULL Constraint FK_Computers_Floors REFERENCES Floors( FloorName),
BldgName VARCHAR(20) NOT NULL CONSTRAINT FK_Computers_Bldg REFERENCES Bldg( BldgName)
)
See, each of the lookups is then an attribute of the computer, and not a discreet entity in its own right. The only way to create an intelligent picture of the computer's location is to discover all of the descriptors. This has nothing to do with key mechanisms. If you put Floor 1 into the floors table 10 times for 10 buildings, I have no idea which floor *entity* you're describing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 30, 2011 at 8:38 am
Sean Lange (11/29/2011)
You can really yourself in trouble with that kind of data structure. You require in the ComputerRoom table a valid Building, Floor and Room. You do now however do any kind of checks to ensure the data is valid. That is why you make this is a hierarchy. Otherwise you have computer that belongs in Room 435, Floor 90, and Building 33. However Building 33 does not have floor 90, that is from building 22 and room 435 belongs to floor 4 in building 8. See where this is going. If the computer belongs to a room, the room belongs to a floor and the floor belongs to a building you don't have to validate all that. Plus you get the advantage of less data redundancy.
Sean,
The entire structure is all five tables with the appropriate keys.
CREATE TABLE [Building]
(
BuildingName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [Floor]
(
BuildingName VARCHAR(20) NOT NULL,
FloorName VARCHAR(20) NOT NULL
CONSTRAINT [PK_Floor] PRIMARY KEY CLUSTERED
(
[BuildingName] ASC,
[FloorName] ASC
))
ALTER TABLE dbo.Floor ADD FOREIGN KEY (BuildingName) REFERENCES dbo.Building (BuildingName)
CREATE TABLE [Room]
(
BuildingName varchar(20) NOT NULL,
FloorName varchar(20) NOT NULL ,
RoomName VARCHAR(20) NOT NULL ,
CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED
(
[BuildingName] ASC,
[FloorName] ASC,
[RoomName] ASC
))
ALTER TABLE dbo.Room ADD FOREIGN KEY (BuildingName, FloorName) REFERENCES dbo.Floor (BuildingName, FloorName)
CREATE TABLE [Computer]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED
)
CREATE TABLE [ComputerRoom]
(
ComputerName VARCHAR(20) NOT NULL PRIMARY KEY CLUSTERED,
BuildingName VARCHAR(20) NOT NULL,
FloorName VARCHAR(20) NOT NULL,
RoomName VARCHAR(20) NOT NULL
)
CREATE INDEX IX_ComputerRoom_Room ON dbo.ComputerRoom (BuildingName, FloorName, RoomName)
ALTER TABLE dbo.ComputerRoom ADD FOREIGN KEY (ComputerName) REFERENCES dbo.Computer (ComputerName)
ALTER TABLE dbo.ComputerRoom ADD FOREIGN KEY (BuildingName, FloorName, RoomName) REFERENCES dbo.Room (BuildingName, FloorName, RoomName)
The composite Foreign Key from ComputerRoom to Room would ensure that only a valid combination of BuildingName, FloorName and RoomName. I would further contend that if only RoomName was used to identify Room then you end up with great Referential Integrity but poor Data Integrity. Given that the example uses a varchar(20) as the key value, you could only have one Room 1. The end users would then create a multitude of Room One, Room Uno, Room Ein, and Room L’un to fit their needs. The same would be true of Floor if only FloorName was only used as the Primary Key. For example many buildings have a second floor so you would end up with the following data in Floor 2, Two, TWO, two, Second, Deux, Zwei, Dos, and Mezzanine.
So then I am thinking about what queries would be executed against the structure there are basically only two. First, given a computer, where is it? Second, given a building, what computers are in the building? If you wanted to know the Names of all the computers on a given floor or in a given room, then you already knew what building you were looking in. The Computer Room Table satisfies all of these queries and each query is covered by an index.
Michael
November 30, 2011 at 9:03 am
michael.french 172 (11/30/2011)
Sean Lange (11/29/2011)
You can really yourself in trouble with that kind of data structure. You require in the ComputerRoom table a valid Building, Floor and Room. You do now however do any kind of checks to ensure the data is valid. That is why you make this is a hierarchy. Otherwise you have computer that belongs in Room 435, Floor 90, and Building 33. However Building 33 does not have floor 90, that is from building 22 and room 435 belongs to floor 4 in building 8. See where this is going. If the computer belongs to a room, the room belongs to a floor and the floor belongs to a building you don't have to validate all that. Plus you get the advantage of less data redundancy.Sean,
The entire structure is all five tables with the appropriate keys.
...snip...
The composite Foreign Key from ComputerRoom to Room would ensure that only a valid combination of BuildingName, FloorName and RoomName. I would further contend that if only RoomName was used to identify Room then you end up with great Referential Integrity but poor Data Integrity. Given that the example uses a varchar(20) as the key value, you could only have one Room 1. The end users would then create a multitude of Room One, Room Uno, Room Ein, and Room L’un to fit their needs. The same would be true of Floor if only FloorName was only used as the Primary Key. For example many buildings have a second floor so you would end up with the following data in Floor 2, Two, TWO, two, Second, Deux, Zwei, Dos, and Mezzanine.
So then I am thinking about what queries would be executed against the structure there are basically only two. First, given a computer, where is it? Second, given a building, what computers are in the building? If you wanted to know the Names of all the computers on a given floor or in a given room, then you already knew what building you were looking in. The Computer Room Table satisfies all of these queries and each query is covered by an index.
Michael
You are correct that the composite keys will give you the validation (somehow skipped right over those when I first read your post :blush:). I think we all agree that using the "names" is not the best approach. I would argue that there are only two queries that would come out of here. That may be true initially but this would likely end up being used by more than computer tracking. Suddenly there would be the ability to track all sorts of other things related to floors and rooms.
I would suggest adding an identity to each table and make them a hierarchy.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 21, 2012 at 6:26 pm
Given that I don't see significant database operations on buildings and floors, I wouldn't isolate them.
Therefore, two tables work nicely and cleanly.
1) create a table for rooms (room_id, room_name, building_name, floor)
2) create a table for computers (computer_id, computer_detail {several columns for this}, user_id {default:0}, room_id {NOT NULL - receiving room?})
Why would I be wrong (besides being too lazy/illiterate to write the sql)?
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply