SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best Practices for Year Field

    Hi all,

    I am building a product "database" (actually a spreadsheet, but I want to try to design it so it will smoothly transition into a database). Some of the SKUs I have will cover more than one year (of a model of motorcycle).

    If I only allow one specific year in the Year field (e.g. 2006), I will have multiple records for the same SKU with the only difference in the records being the Year field.

    Should I instead use a year "range"? If so, what's the best way for me to implement that?

    Should I just embrace the redundant SKUs and specify a unique year for each record?

    Thanks,

    Chris

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,807
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Once you get it to a proper database you will only want one year in the field. You'd have all the common info in a different table referenced by another field alongside the year.

    You could do this in a spreadsheet by using separate pages in the spreadsheet for each table of your "database".
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Member
    Join Date
    Mar 2009
    Location
    Orlando, FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There has to be something UNIQUE about each record ie) VIN number.. If not you should create one.. I really don't understand what you mean by what you wrote though. It would help if I could see a excel file of a small part of the data you are talking about

  4. #4
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks felgall and lasthomepage,

    Reading your posts, I think I've deduced the following:

    My unique key would be say, the SKU. Then I would have the specifics of the SKU in one table (which in this case are hoses and fittings), and the list of applicable models in another table.

    So the records defining single SKUs in the "SKU" table would have a one-to-many relationship with the "MODELS" table.

    Chris

  5. #5
    SitePoint Member
    Join Date
    Mar 2009
    Location
    Orlando, FL
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sounds about right.. so basically it should look like this though for your future knowledge

    TABLE - PRODUCT_SKU
    SKU | PART # | DESCRIPTION
    1234 | ABCD-12 | 2.5" hose fitting

    TABLE - MOTOR_CYCLES
    CYCLE_ID | YEAR | MAKE | MODEL
    1 | 2006 | YAMAHA | XT123
    2 | 2005 | YAMAHA | XT123

    TABLE - PRODUCTS_CYCLES
    SKU | CYCLE_ID
    1234 | 1
    1234 | 2

    This is how you should set it up if A SKU can be on many bikes.. becuase A) bike can have many SKU's associated to it... this is called a MANY to MANY relationship


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •