Only one active price
-
10 июня 2012 г. 0:35
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 г. 3:16Модератор
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 AnjumMicrosoft Community Contributor 10 июня 2012 г. 10:17
- Помечено в качестве ответа emperorIT 10 июня 2012 г. 18:37
-
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. “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 г. 10:12
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:32
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 1Or 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
|
Blog: MSBICOE.com |
MCITP - BI, SQL Developer & DBA Hate to mislead others, if I'm wrong slap me. Thanks!
- Изменено Steven Wang - Shangzhou 10 июня 2012 г. 10:34
.png)
