SQL Server 2012 - forming relationship between tables
-
This is my first database project and I am having difficulties forming the correct relationship between two tables. The database is storing inventory records for approximately 20 buildings, and the room numbers in many of the building are numbered the same. One table has columns like Asset Number, PO Number, Equipment Type, Manufacturer, Model Number, Serial Number. The PK is the Asset Number. The second table has Building Name, Room Number, Room Type, Staff Name. The key for this table is a combination of Building Name and Room Number. I need both to uniquely identify the location. My question is, how do I go about creating the relationship between the tables. The relationship would be described as each Asset having a unique Building/Room Number, but each Building/Room Number can have many Assets. When I try to create the one-to-many relationship it tells me "Both sides of a relationship must have the same number of columns"
-
This is my first database project and I am having difficulties forming the correct relationship between two tables. The database is storing inventory records for approximately 20 buildings, and the room numbers in many of the building are numbered the same. One table has columns like Asset Number, PO Number, Equipment Type, Manufacturer, Model Number, Serial Number. The PK is the Asset Number. The second table has Building Name, Room Number, Room Type, Staff Name. The key for this table is a combination of Building Name and Room Number. I need both to uniquely identify the location. My question is, how do I go about creating the relationship between the tables. The relationship would be described as each Asset having a unique Building/Room Number, but each Building/Room Number can have many Assets. When I try to create the one-to-many relationship it tells me "Both sides of a relationship must have the same number of columns"
All of the primary key columns from the table on the "one" side need to be present in the table on the "many" side. In this case, you would add both building name and room number to your assets table, and specify both columns in the
FOREIGN KEYrelationship. I'd be inclined to move the buildings out to a separate table with a surrogate key, since the value is likely to be repeated across many rows in the rooms table. Similarly, room type and staff name might be better in their own lookup tables, depending on your data.Buildings
BuildingId (PK)
BuildingNameRooms
BuildingId (PK, FK->Buildings)
RoomNumber (PK)
...Assets
AssetNumber (PK)
BuildingId (FK->Rooms)
RoomNumber (FK->Rooms)
...
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
All of the primary key columns from the table on the "one" side need to be present in the table on the "many" side. In this case, you would add both building name and room number to your assets table, and specify both columns in the
FOREIGN KEYrelationship. I'd be inclined to move the buildings out to a separate table with a surrogate key, since the value is likely to be repeated across many rows in the rooms table. Similarly, room type and staff name might be better in their own lookup tables, depending on your data.Buildings
BuildingId (PK)
BuildingNameRooms
BuildingId (PK, FK->Buildings)
RoomNumber (PK)
...Assets
AssetNumber (PK)
BuildingId (FK->Rooms)
RoomNumber (FK->Rooms)
...
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Thank You Richard, I will take your suggestion and do a bit of experimentation. It's clearly the case that a few hours of video at an online training site has not fully educated me. LOL... But it may be enough to get me through this project. I will try and find ( and study ) some documentation on database theory when I get a chance. But until then... Thanks
-
Thank You Richard, I will take your suggestion and do a bit of experimentation. It's clearly the case that a few hours of video at an online training site has not fully educated me. LOL... But it may be enough to get me through this project. I will try and find ( and study ) some documentation on database theory when I get a chance. But until then... Thanks
As a STRONG suggestion you should devote a couple of days to a basic database design book. The consequences of getting your data structure wrong will cost you many multiples of that investment down the track. Depending on the criticality of the project I would even consider getting a professional to do the basic DB design. There is also a site with sample schemas (I have lost the link) that will give you some excellent pointers.
Never underestimate the power of human stupidity RAH
-
Thank You Richard, I will take your suggestion and do a bit of experimentation. It's clearly the case that a few hours of video at an online training site has not fully educated me. LOL... But it may be enough to get me through this project. I will try and find ( and study ) some documentation on database theory when I get a chance. But until then... Thanks
To somewhat piggy-back on the previous suggestions, I would recommend that you consider using an Identity column for your Primary Key and then using the PK to accomplish the links beween tables. For Example: Table: Buildings BuildingID [int] Primary Key Identity (1-1) BuildingName [VarChar] (25) ... Table: BuildingRooms BuildingRoomID [int] Primary Key Identity (1-1) BuildingID INT RoomName [Varchar] (10) ... Table: Assets AssetID [int] Primary Key Identity (1-1) AssetNumber [varchar] (25) AssetName [VarChar] (50) BuildingRoomID [int] ... By using using the Identity columns for the PK's of the tables, you can make your joins much more easily. You may also want to add some Unique Indexes on, for instance, the BuildingName, RoomName, and AssetNumber columns just to make sure someone doesn't accidentally add those items multiple times. ;-) Check out the following link regarding what is termed "normalization" . . . which is what we have suggested with regard to your tables. ;-) http://www.dbnormalization.com/rules-of-normalization-i
-
To somewhat piggy-back on the previous suggestions, I would recommend that you consider using an Identity column for your Primary Key and then using the PK to accomplish the links beween tables. For Example: Table: Buildings BuildingID [int] Primary Key Identity (1-1) BuildingName [VarChar] (25) ... Table: BuildingRooms BuildingRoomID [int] Primary Key Identity (1-1) BuildingID INT RoomName [Varchar] (10) ... Table: Assets AssetID [int] Primary Key Identity (1-1) AssetNumber [varchar] (25) AssetName [VarChar] (50) BuildingRoomID [int] ... By using using the Identity columns for the PK's of the tables, you can make your joins much more easily. You may also want to add some Unique Indexes on, for instance, the BuildingName, RoomName, and AssetNumber columns just to make sure someone doesn't accidentally add those items multiple times. ;-) Check out the following link regarding what is termed "normalization" . . . which is what we have suggested with regard to your tables. ;-) http://www.dbnormalization.com/rules-of-normalization-i
I've spent some time looking into normalization and currently my tables should be in 3rd Normal Form. I also have my application talking to the remote SQL server via the Entity Framework, so I'm getting there. Thanks all for your suggestions, they were most helpful. Rick...
