locked
Having a hard time understanding indexes RRS feed

  • Question

  • I have an Asset database I created that I use to track my physical and virtual assets. I recently created an assignment table to place physical assets that I got rid of so that I can still keep a record of owning the asset but it will show as being inactive for historical reasons.

    The 'Inactive' table has two fields:

    HardwareId datatype =  int = PrimaryKey

    SerialNumber datatype = varchar(150)

    I have a master table called 'Hardware' that has the matching fields:

    HardwareId datatype =  int = PrimaryKey

    SerialNumber datatype = varchar(150) = Unique Index

    When I went to assign a Unique Index to the 'Inactive' table on the column 'SerialNumber' I noticed that the default column SQL Management Studio chose as the index column was the 'HardwareId' column even though I had the 'SerialNumber' column selected when I went to create the Unique Index.

    Should I be leaving the index as HardwareId(ASC) for SerialNumber? I'm confused on how that works.

    What I want is that the column 'HardwareId' in table 'Hardware' be a PK and in the table 'Inactive' be the PK and FK to the PK in Hardware.

    I want the column 'SerialNumber' in table 'Hardware' to be Unique so that no one can assign that SerialNumber by accident to another hardware that may get added to that table.

    Lastly, I want the column 'SerialNumber' in the 'Inactive' table to be a Unique constraint as well, but also an FK to the column 'SerialNumber' in the 'Hardware' table.

    I appreciate any help I can get in understanding and fixing this! Thanks! :)


    Thanks! Andrew

    Thursday, July 19, 2012 2:54 PM

Answers

  • Unless you are going to have more than one row per inactive piece of hardware (like tracking staus and allocation,) I owuld probably suggest that you make this just one table, or perhaps make the Inactive table more of a subclass to the Hardware table. The big problem with the proposed design is that it is not obvious to anyone that the hardware is inactive without joining to another table. Forget that join, and you are now allocating inactive hardware.

    So I would think either:

    CREATE TABLE Hardware 
    (
    HardwareId INT
    NOT NULL CONSTRAINT PK_Hardware PRIMARY KEY ,
    SerialNumber VARCHAR
    (150) NOT NULL CONSTRAINT AK_Hardware  UNIQUE,
    StatusCode  varchar(10) NOT NULL CHECK (StatusCode in ('Active','Inactive'))  
    -- other column. ... 
    );
    GO
    CREATE TABLE InactiveHardware --row should only exist when Hardware 
    (
    HardwareId INT
    NOT NULL CONSTRAINT PK_InactiveHardware PRIMARY KEY
                                           CONSTRAINT FKInactiveHardware_Hardware REFERENCES Hardware(HardwareId)
    ,
                                           --no need for additional key since as a subclass of Hardware, Hardware table natural key is the same as this one's

    InactiveDate DATETIME
    NOT NULL,
    Reason VARCHAR
    (3000) NOT NULL
    );
    GO

    Alternatively, you might consider moving inactive hardware rows out of the hardware table completely. That would make certain that InactiveHardware was completely out of the picture:

    CREATE TABLE Hardware 
    (
    HardwareId INT
    NOT NULL CONSTRAINT PK_Hardware PRIMARY KEY ,
    SerialNumber VARCHAR
    (150) NOT NULL CONSTRAINT AK_Hardware  UNIQUE
    -- other column. ... 
    );

    CREATE TABLE InactiveHardware 
    (
    HardwareId INT
    NOT NULL CONSTRAINT PK_InactiveHardware PRIMARY KEY , --still hardwareId, probably preserve surrogate from earlier
    SerialNumber VARCHAR
    (150) NOT NULL CONSTRAINT AK_InactiveHardware  UNIQUE
    StatusCode  varchar(10) NOT NULL CHECK (StatusCode in ('Active','Inactive')) 
    InactiveDate DATETIME NOT NULL,
    Reason VARCHAR
    (3000) NOT NULL
    -- useful other column values ...  
    );

    I would favor the latter, but I would be wary of the requirements that would make that not possible. (Like it being inactive, but still allocated to a situation for removal. Perhaps with a post-dated inactive date...


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Friday, July 20, 2012 7:09 PM

All replies

  • I think you may be over thinking this, Unless there is more to these tables then you have described. I would recommend that you do not create a second table; but simply add a new date (or Datetime) column called "InactiveDate" to the "assignment" table.  When you de

      activate the device, simply update the existing record with the date the device was removed.

    Bill


    William F. Kinsley

    As far as the indexing goes (forgot that was your real question),  Index the table as you plan to use it or the type of constraint you want the DB to enforce. Also, I avoid using SSMS and just create the indexes and keys directly via SQL. It helps prevent SSMS from guessing what you really are going to be doing.

    In this case, since both HardwareId and SerialNumber are Unique, either can be used to search for a device. 

    For example, if you only use SerialNumber  to join or filter a query, then you only need to index that column since that is what the execution plan will use.

    If you want SQL to enforce an Unique constrain on the HardwareId, then that would justify a  Unique index on it, even though you are not going to be using for joining and searching operations.

    In this case I suspect that SSMS it making HardwareId  the preferred index since it thinks its a FK to the assignment table and thinks you will be joining the two tables via it.

    Bottom line, only index what you need otherwise you are only wasting storage space

    • Edited by WFKinsley Thursday, July 19, 2012 7:40 PM
    Thursday, July 19, 2012 7:06 PM
  • The Hardware table is just a container to hold specifics about the hardware itself such as DatePurchased, ModelName, ModelNumber, SerialNumber, PurchaseAmount, TaxAmount, etc..

    I also have a People table where there are specifics about people such as FirstName, LastName, DateOfBirth, PrimaryEmail, SecondaryEmail etc..

    From there I have an assignment table that takes the PersonId from the People table and the HardwareId from the hardware table and pair them up into the assignment table of 'PeopleAssignedHardware'.

    I very well may be over thinking the Inactive table, but the logic was, once I enter data into the Hardware table, I never want to have to touch it again but rather create assignment tables against it. I guess I may have overkilled it because there is nothing to assign the HardwareId to in the Inactive table which pretty much defeats the idea of an assignment table I guess.

    My logic behind the Inactive table was to also add a column called 'ReasonInactive' with varchar(300) datatype so I can leave an explanation as to why that hardware is Inactive. Although, I guess I could also add two new columns to the Hardware table called 'DateInactive' and 'ReasonInactive'.

    So.. am I over thinking? I'm kind of new to this so any pointers are greatly appreciated.


    Thanks! Andrew

    Thursday, July 19, 2012 7:42 PM
  • If you are planning  or even think you are going to want to add more details specific to the Inactive table, then that may justify creating it. However, I would avoid redundant data. If the HardwareId is the PK to the parent table, then you only need to bring it over and leave the SerialNumber out of the new table.

    So you are thinking of something like this?

    CREATE TABLE Hardware  
    (
    	HardwareId INT NOT NULL,
    	SerialNumber VARCHAR(150) NOT NULL
    	-- other column. ...  
    );
    GO
    ALTER TABLE Hardware  ADD CONSTRAINT PK_Hardware PRIMARY KEY CLUSTERED (HardwareId);
    GO
    
    CREATE TABLE Inactive 
    (
    	HardwareId INT NOT NULL,
    	InactiveDate DATETIME NOT NULL,
    	Reason VARCHAR(3000) NOT NULL
    );
    GO
    ALTER TABLE Inactive  ADD CONSTRAINT PK_Inactive PRIMARY KEY CLUSTERED (HardwareId);
    GO
    

    Bill

     

    William F. Kinsley

    Thursday, July 19, 2012 7:56 PM
  • Unless you are going to have more than one row per inactive piece of hardware (like tracking staus and allocation,) I owuld probably suggest that you make this just one table, or perhaps make the Inactive table more of a subclass to the Hardware table. The big problem with the proposed design is that it is not obvious to anyone that the hardware is inactive without joining to another table. Forget that join, and you are now allocating inactive hardware.

    So I would think either:

    CREATE TABLE Hardware 
    (
    HardwareId INT
    NOT NULL CONSTRAINT PK_Hardware PRIMARY KEY ,
    SerialNumber VARCHAR
    (150) NOT NULL CONSTRAINT AK_Hardware  UNIQUE,
    StatusCode  varchar(10) NOT NULL CHECK (StatusCode in ('Active','Inactive'))  
    -- other column. ... 
    );
    GO
    CREATE TABLE InactiveHardware --row should only exist when Hardware 
    (
    HardwareId INT
    NOT NULL CONSTRAINT PK_InactiveHardware PRIMARY KEY
                                           CONSTRAINT FKInactiveHardware_Hardware REFERENCES Hardware(HardwareId)
    ,
                                           --no need for additional key since as a subclass of Hardware, Hardware table natural key is the same as this one's

    InactiveDate DATETIME
    NOT NULL,
    Reason VARCHAR
    (3000) NOT NULL
    );
    GO

    Alternatively, you might consider moving inactive hardware rows out of the hardware table completely. That would make certain that InactiveHardware was completely out of the picture:

    CREATE TABLE Hardware 
    (
    HardwareId INT
    NOT NULL CONSTRAINT PK_Hardware PRIMARY KEY ,
    SerialNumber VARCHAR
    (150) NOT NULL CONSTRAINT AK_Hardware  UNIQUE
    -- other column. ... 
    );

    CREATE TABLE InactiveHardware 
    (
    HardwareId INT
    NOT NULL CONSTRAINT PK_InactiveHardware PRIMARY KEY , --still hardwareId, probably preserve surrogate from earlier
    SerialNumber VARCHAR
    (150) NOT NULL CONSTRAINT AK_InactiveHardware  UNIQUE
    StatusCode  varchar(10) NOT NULL CHECK (StatusCode in ('Active','Inactive')) 
    InactiveDate DATETIME NOT NULL,
    Reason VARCHAR
    (3000) NOT NULL
    -- useful other column values ...  
    );

    I would favor the latter, but I would be wary of the requirements that would make that not possible. (Like it being inactive, but still allocated to a situation for removal. Perhaps with a post-dated inactive date...


    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Friday, July 20, 2012 7:09 PM