none
Only one active price

    Вопрос

  • Hello

    I have a table that has an 'productid', 'price' and 'active' columns.

    Each id has must only one active price. How do i implement table?  

    primarykey(productID, active) conflicts with an element has three prices (two inactive, one active)

    productid    price    active
    ------------   --------  ---------
    p001            50          1
    p001            45          0
    p001            30          0
    p002            65          1

    10 июня 2012 г. 0:35

Ответы

Все ответы

  • If you're using SQL Server 2008 and up, you can implement filtered unique index. Take a look at this blog post

    http://aboutsqlserver.com/2010/09/12/sunday-t-sql-tip-uniqueness-of-nullable-field/

    You can implement ideas that shown in this blog (if you're using SQL Server 2005, scroll down)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Предложено в качестве ответа Syed Qazafi Anjum 10 июня 2012 г. 10:17
    • Помечено в качестве ответа emperorIT 10 июня 2012 г. 18:37
    10 июня 2012 г. 3:16
  • Please post DDL, so that people do not have to guess what the keys (you have none), constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums. “Active” is a status value and not an attribute.

    To track the history of, say, Prices we need to see time as a continuum and model it as (begin_date, end_date) pairs that define when a foobar had a particular value. Here is the skeleton.

    CREATE TABLE Price_History

    (product_id CHAR(9) NOT NULL,

    start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,

    end_date DATETIME, --null means current

    CHECK (start_date <= end_date),

    unit_price DECIMAL (9,2) NOT NULL,

    PRIMARY KEY (product_id, start_date));

    When the end_date is NULL, that state of being is still current. You use a simple query for the status on any particular date;

    SELECT *

    FROM Price_History

    WHERE @in_cal_date

    BETWEEN start_date

    AND COALESCE (end_date, CURENT_TIMESTAMP);

    There are more tricks in the DDL to prevent gaps, etc


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    10 июня 2012 г. 3:43
  • Please post DDL, so that people do not have to guess what the keys (you have none), constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.



    This is minimal polite behavior on SQL forums.

    Please ignore Joe Celko. "Minimal politeness" is indeed his trademark. That is, occasionally he is helpful, but most of the time he is neither helpful nor polite.

    Naomi's answer is spot on. Filtered index is the way to go in SQL 2008. In earlier versions the solutions are more of a kludge.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    10 июня 2012 г. 10:12
  • without the timestamp for the price startdate and enddate it is pointless to keep your historical price info in your table.

    So my idea is don't keep your inactive info in your table, in that case you will always only have the active price.

    you can store the historical price records into another table like price_hist etc.

    If this is not you want, you might add another column called price_change_sequence, so for your data could look like this:

    productid    price    active        price_change_seq
    ------------   --------  ---------  ---------------------
    p001            50          1          3
    p001            45          0          2
    p001            30          0          1
    p002            65          1          1

    Or maybe you prefer to give the active price the sequence 1, and last change 2, and so on.

    in this case, you can create a combination primary key on (productid, price_change_seq)

    If you only concern the query performance, then like Naomi suggested a filtered index will be a good option and you don't need to create the primary key on your table but a clustered index


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!


    10 июня 2012 г. 10:32