Design question (subclassing)

Question

Design question (subclassing)

tswg2002

SSC-Addicted

Points: 416
More actions
September 12, 2005 at 1:31 am

#91071

Greetings,
I am designing a small IT inventory database and ended up using what I believe is subclassing. I am curious to hear others opinions on this design and if there is an alternative. The problem is that since most items in inventory, CPU, monitors, printers etc, have attributes that don't apply to anything but themselves, I created one INVENTORY MASTER table with a one to one relationship with a separate table for each category such as CPU, monitor etc. I implement a trigger on each of the specialized tables so that only items that belong to that category will be put in the table. The abbreviatedstructure is as follows. All constructive comments are welcome! Thanks for looking.
CREATE TABLE [dbo].[InventoryMaster] (
[InventoryID] [varchar] (15) NOT NULL ,
[CategoryID] [smallint] NOT NULL ,
[ManufacturerID] [smallint] NOT NULL ,
[SerialNumber] [varchar] (25) NOT NULL ,
[PurchaseDate] [smalldatetime] NULL ,
[ReceivedDate] [smalldatetime] NULL ,
[Price] [money] NULL ,
[LengthOfWarranty] [tinyint] NULL ,
[PONumber] [int] NULL ,
[RecordCreatedBy] [varchar] (20) NOT NULL ,
[RecordCreatedDate] [datetime] NOT NULL ,
[RecordLastUpdatedBy] [varchar] (20) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Categories] (
[CategoryID] [smallint] IDENTITY (1, 1) NOT NULL ,
[CategoryName] NOT NULL ,
[CategoryDescription] [varchar] NOT NULL ,
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Monitors] (
[InventoryID] [varchar] (15) Not NULL ,
[ScreenSize] [tinyint] NOT NULL ,
[FlatPanel] [bit] NOT NULL ,
[RecordCreatedBy] [varchar] (20) NOT NULL ,
[RecordCreatedDate] [datetime] NOT NULL ,
[RecordLastUpdatedBy] [varchar] (20) NOT NULL ,
[RecordLastUpdatedDate] [datetime] NOT NULL
) ON [PRIMARY]
CREATE TRIGGER INS_CheckCategory ON Monitors
FOR INSERT
AS
IF (NOT EXISTS (SELECT IM.CategoryID
FROM InventoryMaster IM
INNER JOIN inserted i on IM.InventoryID = I.InventoryID And IM.CategoryID = 2))--ID 2 is for monitor
BEGIN
RAISERROR('This table only accepts monitors ',16,1)
ROLLBACK TRANSACTION
END

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i would have a separate table that has a reference to the InventoryMaster Table and the Category table; I'm awful sure going down the road, you will have multiple items that belong in two categories, and your current design would require two or more entries in the Inventory Master Table in order for an item to exist in multiple categories.

Also, why does the InventoryMaster table have a PONumber attribute? shouldn't the Purchase Order be a separate table, and that table references the InventoryID from InventoryMaster, instead of the reverse?

Since the item must exist before the item is sold, the PONumber will always be null, right? Only when a purchase order is created would you possibly update the PONumber column. it really should not be there, and the [PurchaseOrderMaster?] table would actually be the proper place for the Two columns to be related together.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tswg2002 SSC-Addicted Points: 416 More actions · Answer 2

Thanks for the reply. As for the PO attribute, what you mention is correct but in our situation we do not purchase everthing we need to inventory. We are a non-profit so we receive items for the duration of the project. Some items are purchased. Ideally this would interfact to a PO system to be able to pull all the purchase details so they would not need to be stored in the Inventory Master table. I will look into your other idea of creating a separate table to tie into the Categories but I can't think of any items that would belong in two separate tables.

Thanks again.

bledu SSChampion Points: 12335 More actions · Answer 3

i will agree with lowell's suggestion. imagine when your boss calls and then says i want a report of all the printers or i want a report of all the peripherals

Everything you can imagine is real.

tswg2002 SSC-Addicted Points: 416 More actions · Answer 4

Thanks for the reply. Yes, I agree with the initial suggestion. However. my main issue is what would happen if the boss decided to start inventorying hand held devices. Then you would need to create another table just for those.

So my question is really, is there any way of avoiding this design of one specialized table for each item with specific features? I really don't see any other options that could answer questions like:

How many PCs do we have with less than 512 meg of ram?

How many network enabled printers do we have?

Lowell SSC Guru Points: 323518 More actions · Answer 5

i would do this with three tables: as new/differet items are added, if they cannot use an existing attribute to describe them, it is added to the lookup table.

A similar table would be added for Categories, that way an item can be a "peripheral" and a "printer"

CREATE TABLE [InventoryMaster] (

InventoryMasterID int identity(1,1) not null

[InventoryCode] [varchar] (15) NOT NULL ,

[CategoryID] int] NOT NULL ,

[ManufacturerID] [int] NOT NULL ,

[SerialNumber] [varchar] (25) NOT NULL )

TBAttribute (

attributeID int identity(1,1) not null primary key,

Sortcode int default(100),

Descrip(varchar(50))

InventoryAttributes (

InventoryAttributes int identity(1,1) not null primary key ,

InventoryMasterID int,

attributeID int,

foreign key (InventoryMasterID) references InventoryMaster(InventoryMasterID),

foreign key (attributeID) references TBAttribute(attributeID))

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tswg2002 SSC-Addicted Points: 416 More actions · Answer 6

Thanks Lowell,

I follow what you are trying to do. I did something similar with a database on survey questions. Rather than hard code each question into the design of a table, I used the same multiple table approach you mentioned.

Thanks again!

bledu SSChampion Points: 12335 More actions · Answer 7

a bit of advice: if you will be writing the sql 2000 exam, there is a question with something along this principle, but using a different domain.

Everything you can imagine is real.

DCPeterson SSCoach Points: 15266 More actions · Answer 8

I have to disagree with the name/value approach to inventory attributes. This at first looks very flexible (and it is) but there are several critical flaws. The first and foremost is that if you are tracking inventories, it can be assumed that there are a defined list of things that you want to inventory AND that each of those things has a defined list of attributes that are IMPORTANT to the enterprise to know. The name/value approach is almost entirely deficient in ensuring that the items in your inventory are defined properly. A correlated problem is that you can't even enforce the most basic of constraints--domain integrity--using this method. The "value" part of the name/value pair would ALWAYS have to be a varchar. But to use your example, someone enters a name/value pair of 'RAM'/'Red', or 'color'/'512'? You would also have a hard time preventing someone from adding a 'CPU' attribute to a desk.

Name/value tables are a mess waiting to happen. This is essentially the same thing I wrote about here

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/

DCPeterson SSCoach Points: 15266 More actions · Answer 9

I do agree with lowell's first suggestion however...

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/