locked
Question regarding proper way to store and query sequential numbers ranges (Product ID's) that contain gaps? RRS feed

  • Question

  • Hello all,

    I'm quite new to databases so please forgive me if I use incorrect terminology. My problem seems quite simple but I'm having some trouble wrapping my head around how to deal with storing (and querying) ranges of numbers in a database.

    What I have is a list of 5 different battery models (which will grow over time to include upwards of 200 models). The model numbers are guaranteed to be unique so I can use them as a primary key in a table. The models each have a number of sequential battery id's associated with them. Unfortunately the battery id's have gaps. For example Model xxx has id's 201-500, 1500-1750; model yyy has 501-700, 1751-2000, etc..

    What I'd like to do is create a table in a database such that I can query on a single battery id and return the model (and some additional information such as manufacturer, capacity, type, etc). I'm not sure what the proper way to set up such a table is. It seems that creating an individual row for each battery id would be an unnecessary waste of space. What I'm having trouble understanding is how to deal with the gaps in the battery id's.

    I'm a bit stuck at the moment so any advice or resources on how to approach the problem would be greatly appreciated.

    Cheers,

    Alex

    Tuesday, September 6, 2011 5:50 PM

Answers

  • OK, i think i may be understanding this a bit better.

    If the individual batteries have properties stored in the TABLE, the appropriate method is to have a Model and Battery TABLE.

    If the ids, however, are merely a record of number printed, a range can be kept in a child TABLE. Something like:

    Model
    -------
    Name
    Description

    Model_Range
    ---------------
    Model
    Id_Begin
    Id_End

    The query would be:

    SELECT
    	Model.Description
    FROM
    	Model,
    	Model_Range
    WHERE
    	Model_Range.Model	= Model.Name
      AND	?			BETWEEN	Model_Range.Id_Begin
    				    AND	Model_Range.Id_End
    

    The difference in between the options are whether the individual battery's id is an object or an attribute.

    • Marked as answer by Just_Alex Wednesday, September 7, 2011 6:37 PM
    Wednesday, September 7, 2011 5:59 PM
    Answerer

All replies

  • One way to go with it is

    1.Create a table called BatteryModel, This table will contain model vs Id's

    2.Use your current table for Battery which contains the data...

    Join these two tables on ID while fetching for a particular type/model

    Tuesday, September 6, 2011 6:48 PM
  • Hi Harshvai,

    I think I've implemented something similar to what you are suggesting. I have one table called BatteryInfo that has model as the primary key along with columns: Manufacturer, Capacity, Type, Notes, etc... I created a second table BatteryIDTable which has BatteryID (integer, primary key) and model (foreign key for the BatteryInfo table) in which I've simply created all the battey id's as rows:

    BatteryIDTAble:

    BatteryID model

    001 xxx

    002 xxx

    003 xxx

    etc...

    Then I search for a battery id and join as you say on model and am able to get all the battery info. Is this considered a reasonable implementation? There are several thousand battery id's (and this could grow to 10's of thousands) and as there are large sections of sequential id's for the same model I wondered if there was a way to store a range as a single row instead of adding hundreds of rows.

    Cheers,

    Alex

    Tuesday, September 6, 2011 7:28 PM
  • Ids do not have to be sequential. If you have chosen "AutoNumber" as the data type, change it to SMALLINT which handles-2^15 (-32,768) to 2^15-1 (32,767) (as explained: http://msdn.microsoft.com/en-us/library/ms187745.aspx)

    To separate the repeated data into a TABLE where it will only be listed once is the very basis or normalization, which is highly desirable in (non-warehouse) databases. So, yes, it is reasonable.

     

    Tuesday, September 6, 2011 8:13 PM
    Answerer
  • I guess what I'm trying to do is take it one step further with respect to repeated data and come up with a way to take thousands of rows of of the BatteryIDTable. For example if the BattID's 0-999 are all model x is there a way to store that information in a single row and query any of the 1000 id's (0-999) to return model x without adding all the rows to a table:

    0 x

    1 x

    ...

    999 x

    I thought of using something similar to columns: MinIDValue, MaxIDValue, model and then using a select statement to check if the value was in between. I'm just not sure if that is taking things too far or making it unnecessarily complicated. So my example would then become a single row:

    0 999 x

    and I would query with something like:

    SELECT model FROM BatteryIDTable WHERE MinIDValue <= myNum AND MaxIDValue >= myNum



    • Edited by Just_Alex Tuesday, September 6, 2011 9:40 PM
    Tuesday, September 6, 2011 8:35 PM
  • Can you please post sample data CREATE TABLE +INSERT INTO and desired result? 
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Wednesday, September 7, 2011 7:34 AM
  • Bad Idea. :)

    What happens if the range changes? What happens if another id is added but the next "range" is already taken? There will be overlapping ranges or all sorts of confusion.

    The appropriate method is to have an FOREIGN KEY to the lookup:

    Model
    --------
    Name
    Description

    Battery
    --------
    Id
    Model (REFERENCES Model)

     

     


    Wednesday, September 7, 2011 12:06 PM
    Answerer
  • Try Following Table Structure and Query

    Create Table BatteryModel(ModelId Int Identity(1,1) Primary Key,ModelName Varchar(100),BatteryType Varchar(20),Manufacturer Varchar(200))
    
    Create Table Model_Identity(Id Int Identity(1,1),ModelId Int References BatteryModel(ModelId),BetteryId Int)
    
    Select A.ModelName,A.Manufacturer,a.BatteryType 
    From BatteryModel A 
    Join Model_Identity B On A.ModelId=B.ModelId
    Where B.BetteryId =100
    
    



    Mark as Answer If Reply Was Helpful Thanks Kuldeep Bisht Technical Lead @ Simplion Technologies http://mssqlguide.kuldeepbisht.com
    Wednesday, September 7, 2011 12:43 PM
  • Hi Brian,

    I forgot to mention that the battey ID's are also unique. Essentially what they've done is already printed off a list of several thousand sequential numbers (ID's) and labeled the batteries as they came in (and will continue to do this for some time). This has created a situation where there are long sequences of ID's (upwards of several hundred) that are the same model, repeated over and over.

    For example: They labeled battery model x with id's 0-999, then battery model y with id's 1000 - 1500, then more of battery model x with id's 1501-2000, and so on... So far this covers 5 separate models with each model having 3 to 4 of these long repeated sequential, unique (for now :) id's. There are no space or performance constraints for this database so I can certainly make a table with only BatteryID and Model and join it with another table that has all the Model, Capacity, etc information.

    I was just wondering what the proper, efficient way to do it was. Is it a normal situation to have a single table with thousands of Id's (as rows) and another column(s) that are used to join to second (third, fourth, etc) tables in this kind of situation or is there a better way to do this?

    Btw, thank you (and everyone else) for your help it is much appreciated.

    Cheers,

    Alex


    • Edited by Just_Alex Wednesday, September 7, 2011 4:04 PM
    Wednesday, September 7, 2011 4:02 PM
  • OK, i think i may be understanding this a bit better.

    If the individual batteries have properties stored in the TABLE, the appropriate method is to have a Model and Battery TABLE.

    If the ids, however, are merely a record of number printed, a range can be kept in a child TABLE. Something like:

    Model
    -------
    Name
    Description

    Model_Range
    ---------------
    Model
    Id_Begin
    Id_End

    The query would be:

    SELECT
    	Model.Description
    FROM
    	Model,
    	Model_Range
    WHERE
    	Model_Range.Model	= Model.Name
      AND	?			BETWEEN	Model_Range.Id_Begin
    				    AND	Model_Range.Id_End
    

    The difference in between the options are whether the individual battery's id is an object or an attribute.

    • Marked as answer by Just_Alex Wednesday, September 7, 2011 6:37 PM
    Wednesday, September 7, 2011 5:59 PM
    Answerer
  • Thanks everyone for the helpful suggestions, much appreciated. I've marked Brian's solution as the answer as it best fits the problem but I do appreciate everyone's input and it's definitely helped me learn a lot about modelling and storing the data.

    Cheers,

    Alex

    Wednesday, September 7, 2011 6:41 PM
  • I would add the you should rename your BatteryInfo table to be something more generic such as Product. Otherwise you may find yourself storing information about watches in a table called BatteryInfo.

    I would also say that your line of thinking is correct. You've raised the question of whether you should store an entry for every ID or a range. This is good question to ask. :-) This is a fairly common design question, for example, if someone goes on holidays is it actually a start date and an end date or individual dates?



    • Edited by MickleKulls Monday, September 12, 2011 4:02 AM
    Monday, September 12, 2011 3:35 AM