Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can provide.

My design includes a Device table which holds the various devices that can be entered into inventory. Each device has a boolean flag, CanNetwork which states whether a device has network capability, e.g., for most computers CanNetwork = true, for hard drives CanNetwork = false; some printers will be true, and others will be false. You get the idea.

The CanNetwork field determines if network-related information is relevant when an inventory record is created.

Design 1

My first design uses an index on Device.DeviceID and Device.CanNetwork to use in a foreign key constraint with the Asset table.

Schema #1: Foreign key constraint with index

The NetworkStatus table looks like this in this setup:

+----------------------------------------------------------------------+
| NetworkStatusID | NetworkStatus  | NetworkStatusDescription          |
|----------------------------------------------------------------------|
| 1               | Connected      | Device connected to network.      |
| 2               | Not Connected  | Device not connected to network.  |
| 3               | Do Not Connect | Do not connect device to network. |
+----------------------------------------------------------------------+

I put check constraints on the Asset table as follows to ensure a network status and network information can only be provided if the device is capable of connecting to a network.

-- Asset table check constraint: CK_CanNetwork
-- If can't network, then network fields are null
CanNetwork = 0
AND NetworkStatusID IS NULL
AND Hostname IS NULL
AND IPAddress IS NULL
AND MACAddress IS NULL
OR CanNetwork = 1

-- Asset table check constraint: CK_NetworkStatus
-- If network status is "Connected", then must provide
-- a hostname or ip address
NetworkStatusID = 1 AND Hostname IS NOT NULL
OR NetworkStatusID = 1 AND IPAddress IS NOT NULL
OR NetworkStatusID <> 1

The issue I have with this design is I'm not sure if the relationship with Asset and Device.DeviceID/Device.CanNetwork is a good or bad design decision. Is propagating a non-key field like CanNetwork to other tables a bad design? I don't have enough experience with database design to make an informed decision.


Design 2

In this design I thought I would use a bridge/association/junction table to decide which network statuses are valid for a device. It looks like this:

enter image description here

The NetworkStatus table looks like this in this setup (Notice the addition of record with id #4, and the ForCanNetwork field which specifies that this status is for use with devices that can can connect to a network):

+--------------------------------------------------------------------------------------+
| NetworkStatusID | NetworkStatus  | NetworkStatusDescription          | ForCanNetwork |
|--------------------------------------------------------------------------------------|
| 1               | Connected      | Device connected to network.      | True  (1)     |
| 2               | Not Connected  | Device not connected to network.  | True  (1)     |
| 3               | Do Not Connect | Do not connect device to network. | True  (1)     |
| 4               | Incapable      | Cannot connect to networks.       | False (0)     |
+--------------------------------------------------------------------------------------+

Due to this design's granularity, I could theoretically allow any mix of statuses for devices with this design, but I wanted to control it so I wrote some triggers to only insert the correct mix of statuses depending on whether the device is network capable. Triggers as follows:

-- NetworkStatus table on INSERT trigger
-- Adds a record for each device that
-- matches the capability of the network status
INSERT INTO DeviceNetworkStatus
SELECT i.NetworkStatusID, dev.DeviceID
FROM Device dev
CROSS JOIN
inserted i
WHERE dev.CanNetwork = i.ForCanNetwork

-- Device table on INSERT trigger
-- Adds a record for each network status that
-- matches the capability of the new device
INSERT INTO DeviceNetworkStatus
SELECT ns.NetworkStatusID, i.DeviceID
FROM NetworkStatus ns
CROSS JOIN
inserted i
WHERE ns.ForCanNetwork = i.CanNetwork

I used the following CHECK constraint on the Asset table:

-- Asset table check constraint: CK_NetworkStatus
-- If network status is "Connected', then must provide
-- a hostname or ip address
-- If network status is "Incapable", then network fields
-- must be null
NetworkStatusID = 1 AND Hostname IS NOT NULL
OR NetworkStatusID = 1 AND IPAddress IS NOT NULL
OR NetworkStatusID = 4 AND Hostname IS NULL
AND IPAddress IS NULL AND MACAddress IS NULL
OR NetworkStatusID <> 1 AND NetworkStatusID <> 4

This design eliminates the need to propagate CanNetwork across the tables. The issue I see with this design is that every device that has network capability will have records in DeviceNetworkStatus paired with NetworkStatus ids 1, 2 and 3, while devices that can't connect to a network will be paired only with NetworkStatus id 4. It seems like a lot of extra records that all mean the same thing: devices that can be networked can only use statuses 1, 2 and 3, and devices that can't network only use 4. This design seems to be more "relationally correct", but also smells a bit.

Update

The following update proposes variations on Design 1. I come across situations like this often, where there are many ways to achieve the same end result. I never know how to tell if there are hidden problems with the designs, and I can't judge when to normalize or denormalize. Is one of these designs preferred over the other and why?

Design 1.1

Schema #1.1: NetworkAsset subtype

-- NetworkAsset table check constraint: CK_CanNetwork
CanNetwork = 1

-- NetworkAsset table check constraint: CK_NetworkStatus
-- If network status is "Connected", then must provide
-- a hostname or ip address
NetworkStatusID = 1 AND Hostname IS NOT NULL
OR NetworkStatusID = 1 AND IPAddress IS NOT NULL
OR NetworkStatusID <> 1

Design 1.2

Schema #1.2: NetworkAsset and NetworkDevice subtype

For the record, this design seems a bit absurd even to me, but this is my thought process. In this one, the presence of a DeviceID in the NetworkDevice table is equivalent to saying CanNetwork = true in Design 1. The NetworkAsset.NetworkDeviceID has a foreign key constraint and is used to ensure only networkable devices are entered. Can do this using a CHECK constraint (see below), and by making NetworkDeviceID a computed column that is equal to DeviceID.

-- NetworkAsset table check constraint: CK_IsNetworkDevice
NetworkDeviceID = DeviceID
share|improve this question
1  
Design 1 is clean, lean, and correct. It can ensure 100% integrity of your data. We use such approaches in our system all the time, and they never fail. Design 2 cannot be implemented without triggers, and as such it is not completely reliable - triggers are not fully reliable. –  AlexKuznetsov Mar 9 '13 at 21:48
    
Thank you Alex. I feel better knowing I'm on the right track with design 1. Do you know if there is any benefit to making a subtype table NetworkAsset? –  TheSecretSquad Mar 10 '13 at 20:27
    
Regarding your subtype table NetworkAsset, can you post your approach. The Devil is in the details, and I do not have enough information to answer your question yet ;) –  AlexKuznetsov Mar 10 '13 at 21:53
    
@AlexKuznetsov I added Design 1.1 and 1.2 to give you an idea of what I'm thinking. I'm interested to hear your thoughts. Thank you. –  TheSecretSquad Mar 11 '13 at 4:27
1  
So far I cannot think of any situation when 1.2 would have any advantage at all. So, 1.1 is preferable, because it does what we want and it is simpler. –  AlexKuznetsov Mar 12 '13 at 2:16

1 Answer 1

I have a surprise for you: you don't need CanNetwork at all!

You have two sets of devices: all devices and devices that can be attached to the network. Only the latter have a network status, and that status can be only one of two values: connected and not connected. (There may be administrative values, too, such as what network it's allowed to attach to, or its netmask or something. Those attributes also do not apply to non-networked devices.)

The presence of a device in the NetworkedDevices table indicates that it can be attached to the network. Absence from that table means it cannot; that is what's known as the Closed World Assumption.

In general, the presence of a boolean valued column that indicates whether or not the thing has a relationship to another table is hint that you need two tables instead of a flag.

HTH.

share|improve this answer
    
I agree with you that in most cases it is not necessary. The reason I include it is so devices can declare whether they are capable of connecting to a network, for example a workstation can connect, some printer models (but not all) can connect, and (most, if not all) monitors cannot connect. I could then use the attribute to disallow users from entering network information for a device such as a monitor, that inherently does not have network capability. Please let me know if this sounds reasonable. –  TheSecretSquad May 23 '13 at 2:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.