Return to Question

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored in a single table, and specific information is put into subtype tables. My dilemma is choosing between the following two designs:

In the top diagram all devices share common subtypes. For example, desktop computers and laptops would have records in the following tables: Device, NetworkDevice. A switch would have records in: Device, NetworkDevice. A router would have records in: Device, NetworkDevice, WANDevice. Any device for which we track location will have a record in Location. Some pros and cons that I thought of for this setup:

• Pro: SELECTing records based on a common field, like Hostname, or LocationID is easier.
• Pro: No null fields.
• Con: Tables that should be included in CRUD operations for a particular device are not obvious, and may confuse future DBAs.

In the bottom diagram all devices have their own subtype (There are more classes of device that are not shown here). In this situation, it is obvious which tables records get inserted to or selected from. Desktop computers and laptops go in Computer, etc. Some pros and cons that I thought of for this setup:

• Pro: It is immediately obvious which tables to use for CRUD operations for subtypes.
• Pro: Only have to use one table for CRUD operations.
• Con: SELECTing records based on common subtype fields requires all tables to be combined, for example searching by Hostname, or LocationID.

In both situations, the ClassDiscriminator field is placed in subtype tables for use with a CHECK constraint to control which types can be inserted.

Are there any recommendations for which design is better, or is it completely a matter of opinion and dependent on the intended purpose of the database?

Thank you in advance for any input provided. Please ask if any additional information is needed.

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored in a single table, and specific information is put into subtype tables. My dilemma is choosing between the following two designs:

In the top diagram all devices share common subtypes. For example, desktop computers and laptops would have records in the following tables: Device, NetworkDevice. A switch would have records in: Device, NetworkDevice. A router would have records in: Device, NetworkDevice, WANDevice. Any device for which we track location will have a record in Location. Some pros and cons that I thought of for this setup:

• Pro: SELECTing records based on a common field, like Hostname, or LocationID is easier.
• Pro: No null fields.
• Con: Tables that should be included in CRUD operations for a particular device are not obvious, and may confuse future DBAs.

In the bottom diagram all devices have their own subtype (There are more classes of device that are not shown here). In this situation, it is obvious which tables records get inserted to or selected from. Desktop computers and laptops go in Computer, etc. Some pros and cons that I thought of for this setup:

• Pro: It is immediately obvious which tables to use for CRUD operations for subtypes.
• Pro: Only have to use one table for CRUD operations.
• Con: SELECTing records based on common subtype fields requires all tables to be combined, for example searching by Hostname, or LocationID.

In both situations, the ClassDiscriminator field is placed in subtype tables for use with a CHECK constraint to control which types can be inserted.

Are there any recommendations for which design is better, or is it completely a matter of opinion and dependent on the intended purpose of the database?

EDIT: A specific question I have regards the overlapping nature of the table "NetworkDevice". This table is meant to hold network information for any device with a hostname and/or IP address, whether it is a computer, switch, or router. Is the overlapping nature of this table something that could cause problems, or is it okay to implement it this way?

Thank you in advance for any input provided. Please ask if any additional information is needed.

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored in a single table, and specific information is put into subtype tables. My dilemma is choosing between the following two designs:

In the top diagram all devices share common subtypes. For example, desktop computers and laptops would have records in the following tables: Device, NetworkDevice. A switch would have records in: Device, NetworkDevice. A router would have records in: Device, NetworkDevice, WANDevice. Any device for which we track location will have a record in Location. Some pros and cons that I thought of for this setup:

• Pro: SELECTing records based on a common field, like Hostname, or LocationID is easier.
• Pro: No null fields.
• Con: Tables that should be included in CRUD operations for a particular device are not obvious, and may confuse future DBAs.

In the bottom diagram all devices have their own subtype (There are more classes of device that are not shown here). In this situation, it is obvious which tables records get inserted to or selected from. Desktop computers and laptops go in Computer, etc. Some pros and cons that I thought of for this setup:

• Pro: It is immediately obvious which tables to use for CRUD operations for subtypes.
• Pro: Only have to use one table for CRUD operations.
• Con: SELECTing records based on common subtype fields requires all tables to be combined, for example searching by Hostname, or LocationID.

In both situations, the ClassDiscriminator field is placed in subtype tables for use with a CHECK constraint to control which types can be inserted.

Are there any recommendations for which design is better, or is it completely a matter of opinion and intended purpose for the database?

Thank you in advance for any input provided. Please ask if any additional information is needed.

I'm building an inventory database that stores IT hardware, such as desktop computers, laptops, switches, routers, mobile phones, etc. I'm using supertype/subtype pattern, where all devices are stored in a single table, and specific information is put into subtype tables. My dilemma is choosing between the following two designs:

In the top diagram all devices share common subtypes. For example, desktop computers and laptops would have records in the following tables: Device, NetworkDevice. A switch would have records in: Device, NetworkDevice. A router would have records in: Device, NetworkDevice, WANDevice. Any device for which we track location will have a record in Location. Some pros and cons that I thought of for this setup:

• Pro: SELECTing records based on a common field, like Hostname, or LocationID is easier.
• Pro: No null fields.
• Con: Tables that should be included in CRUD operations for a particular device are not obvious, and may confuse future DBAs.

In the bottom diagram all devices have their own subtype (There are more classes of device that are not shown here). In this situation, it is obvious which tables records get inserted to or selected from. Desktop computers and laptops go in Computer, etc. Some pros and cons that I thought of for this setup:

• Pro: It is immediately obvious which tables to use for CRUD operations for subtypes.
• Pro: Only have to use one table for CRUD operations.
• Con: SELECTing records based on common subtype fields requires all tables to be combined, for example searching by Hostname, or LocationID.

In both situations, the ClassDiscriminator field is placed in subtype tables for use with a CHECK constraint to control which types can be inserted.

Are there any recommendations for which design is better, or is it completely a matter of opinion and dependent on the intended purpose of the database?

Thank you in advance for any input provided. Please ask if any additional information is needed.