Detailed information in aggregate report

Forgive me for asking, but I've looked around and couldnt
quite find the best method of achieving the following.
I've got a report that aggregates all sortsa data with
group by and sums and counts etc.
What I'd like to do, is if a parameter is set, use same
columns etc, but bring up details on a particular
subscriber. (one row per subscriber)
Which would require one more column for subscriber name
etc. But can I easily turn off the aggregation stuff in
the query itself?Weston,
Have you taken a look at the Microsoft SQL Server Report Pack for
Financial Reports (free download). In particular the report
"IncomeStmtCurYTD-Region" may be the effect you're looking for with the
"show all" parameter? The "Territory Sales Drilldown" included in the
Sample Reports provides the ability to "drill" into the detail.
Regards,
Paul

Design: bit on or off?

I'm asking lots of design questions here.. but they are little ones,
sometimes matter of taste, sometimes more than that.
Imagine I have a table of "Keys". Some of these keys will be "active" and
some will be "blocked".
I'm trying to decide whether I should use a BIT column and call this
"active" or "blocked".
So should bit 1 mean "active" or mean "blocked" ? There will almost
certainly be more active than blocked keys. Which one is more intuitive or
likely convenient in practice?
Of course I could use a set "yes/no" or "active/disabled" but for only 2
possibilities, a bit seems more efficient and convenient in front en back
end. What are your recommendations/tastes?
LisaHi, Lisa
If you use a bit column, 1 should represent true and 0 should represent
false. So, if the column name is "active", 1 means that the key is
active, 0 means that it's blocked.
However, you should consider using a char(1) column with a constraint
like "Status IN ('A','B')", because it is possible that sometime in the
future you may want another status value, for example "pending". If you
use a codification on a char(1), make sure that it's meaning is well
documented (for example in the Description of the column, if you use
Enterprise Manager).
Razvan|||If "active" basically means "enabled" or "on" or "true", then use 1 and 0
for "blocked".
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:u5kVzK09FHA.4004@.TK2MSFTNGP14.phx.gbl...
> I'm asking lots of design questions here.. but they are little ones,
> sometimes matter of taste, sometimes more than that.
> Imagine I have a table of "Keys". Some of these keys will be "active" and
> some will be "blocked".
> I'm trying to decide whether I should use a BIT column and call this
> "active" or "blocked".
> So should bit 1 mean "active" or mean "blocked" ? There will almost
> certainly be more active than blocked keys. Which one is more intuitive or
> likely convenient in practice?
> Of course I could use a set "yes/no" or "active/disabled" but for only 2
> possibilities, a bit seems more efficient and convenient in front en back
> end. What are your recommendations/tastes?
> Lisa
>|||Lisa Pearlson wrote:
> I'm asking lots of design questions here.. but they are little ones,
> sometimes matter of taste, sometimes more than that.
> Imagine I have a table of "Keys". Some of these keys will be "active" and
> some will be "blocked".
> I'm trying to decide whether I should use a BIT column and call this
> "active" or "blocked".
> So should bit 1 mean "active" or mean "blocked" ? There will almost
> certainly be more active than blocked keys. Which one is more intuitive or
> likely convenient in practice?
> Of course I could use a set "yes/no" or "active/disabled" but for only 2
> possibilities, a bit seems more efficient and convenient in front en back
> end. What are your recommendations/tastes?
> Lisa
I'd prefer to use a CHAR or maybe an INT status code. That way, you can
add more statuses if you need to, you can use a meaningful readable
code that everyone can understand and you avoid some of the peculiar
quirks of the BIT type (for example some numeric operators are valid
for BIT and others aren't).
David Portas
SQL Server MVP
--

Design type question

Out of curosity I am asking this design type question because I am curious as to what thoughts you fine folks have on the subject matter.

I have seen many instances of a databse design where there are detail records that have a dollar value associated with them such as an invoice table which holds invoice records. I have noticed that no where is a total of the invoices stored, causing SQL to have to run through the records each time a total is needed.

In days gone by prior to SQL, data structures would be such that a total accumulator field in a data file somewhere would be updated as invoices were processed so that the system would not have to run through the individual records to derive the total of them.

I am familiar with normalization in terms of SQL as I have worked with SQL for years and of course I have work with non-SQL technologies before SQL came onto the scene. What I am curious to know is that is it such a bad thing in the SQL world to have something like an invoice total accumulator that constantly is updated as invoices are processed and queries can access this total accumulator directly rather than having to gather the individual invoice records and total them up first before presenting the total to the user?

If a total field is kept, you have the same data in two places. If you have the same data in two places, they can get out of sync.

Say I have three items at 5, 10 and 20 dollars and a total field of 35 dollars.

Sam, a student intern, edits an item and changes it from 5 to 7 dollars but doesn't recalculate the total field (because he's an intern and doesn't know).

Now the data is corrupt, the total field still has 35 dollars, but the real total is 37 dollars.

|||Actually I wasn't thinking in terms of a field that the user had access to thr the UI. Instead, a table for invoice id and invoice total, stored procedures would handle updating the invoice total in the table when the invoice itself from the invoice table was modified or new invoices added. Then when a total was needed for whatever reason the query could go right to the table and obtain the total rather than crunching the number.|||

It's a good question, but in the scenario you presented you are atucally causing much more harm than good. Here's why...

irasmith:

stored procedures would handle updating the invoice total in the table when the invoice itself from the invoice table was modified or new invoices added.

Now you are running that harsh code every time someone does an insert, or update... whereas if you just let SQL do the mathonly when it is being requested to be seen, it's much better. Also, as was mentioned above, it could get out of sync.

There is a place for storing, or "caching" the calculated value:

A friend of mine has just launched a HUGE project for the County, and in it there are certain bugeting calculations that take about 2 minutes to calculate. In this kind of invironment, caching (by storing the calculated value into a table) would not be so bad :P

That's my 2 cents :P

Peace,

|||I once worked on a project where once a day they ran a stored procedure that pulled data from another database and ran calculations on it, and then stored the information in a format that was easily read into reports, including precalculated totals. Of course the proc took about half an hour to run (don't ask) so there was no way you'd want to wait that long for a single invoice. As Nullable wrote, its pretty situational, but there are definately times you would want to 'cache' the data like this.|||

Thanks to all who have posted to this thread. Naturally I am not one to run out and just do something out of the norm, however, as I work more in the design area I am finding that sometimes you have to weigh things and accept a trade off in order to accomplish the main goal. It is good to know others have had similar type issues arise before and while never an easy choice we just have to look at the specific situation and go from there.

design question : the sub-entities cohesion problem

Hello,
I am in the process of designing a big commercial database for a
wholesaler.
Since many months now, I have been asking myself questions about how to
physically implement a system of tables that would efficiently
represent the logical situation of many "sub-entities" related to a
single "master entity".
Here is a simplified example :
A company has a catalogue of many products, grouped in 3 Categories :
toys products, books products and CDs products.
We agree that each of the categories should have its own physical
table, since CDs are different from books, etc.
We also agree that all categories share similar informations such as a
price, a name, a purchase date, etc. ... this legitimate the creation
of a single "products" table. With that table, we will also manage the
products IDs, since we dont want duplicate among categories.
Therefore, we have 4 tables to create : Products, Products_Books,
Products_Toys and Products_CDs.
The unique ID of a product is hold in the Products table. The
sub-tables hold category-oriented information. Each sub-tables refer
to the master table with a many-to-1 FK.
First problem : how can I physically make sure that a product exist in
only one sub-table? Of course, this is managed at the application
level ... but how can I put a database-constraint to physically avoid a
situation where a product entry from the Products table would have both
a sub-entry in the book AND the toy sub-table.
Second problem : how can I physically make sure that all products in
the master table have 1 entry in a sub-table. Or, if you prefer, how
can I make sure that the FK between the master and the sub-entities is
a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
All answers will be appreciate.
thank you,
BTGGoogle around for someof my postings on modeling classes in SQL.|||before.the.gods@.gmail.com wrote:
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
>
CREATE TABLE products (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL CHECK (product_type IN ('B','A','T' /*
Books, Audio, Toys */)), product_name VARCHAR(50) NOT NULL UNIQUE, /*
... attributes common to all product types */ UNIQUE (product_type,
sku_code));
CREATE TABLE books (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL DEFAULT 'B' CHECK (product_type = 'B'),
FOREIGN KEY (product_type, sku_code) REFERENCES products (product_type,
sku_code));
CREATE TABLE audio (sku_code CHAR(13) NOT NULL PRIMARY KEY,
product_type CHAR(1) NOT NULL DEFAULT 'A' CHECK (product_type = 'A'),
FOREIGN KEY (product_type, sku_code) REFERENCES products (product_type,
sku_code));
CREATE TABLE toys (sku_code CHAR(13) NOT NULL PRIMARY KEY, product_type
CHAR(1) NOT NULL DEFAULT 'T' CHECK (product_type = 'T'), FOREIGN KEY
(product_type, sku_code) REFERENCES products (product_type, sku_code));

> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
>
Populate the parent and the referencing table in the same proc. You can
use a trigger to prevent deletes that violate the rule but 1-1
cardinality is hard to enforce through constraints because SQL Server
doesn't support deferrable constraints.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Store the common columns in each sub-entity table, and then create a view
that is the union of the common columns. You'll need triggers on all of the
sub-entity tables to prevent duplicate entries and if you want to be able to
modify the product view, then you'll need instead of triggers on it.
<before.the.gods@.gmail.com> wrote in message
news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
> Hello,
> I am in the process of designing a big commercial database for a
> wholesaler.
> Since many months now, I have been asking myself questions about how to
> physically implement a system of tables that would efficiently
> represent the logical situation of many "sub-entities" related to a
> single "master entity".
> Here is a simplified example :
> A company has a catalogue of many products, grouped in 3 Categories :
> toys products, books products and CDs products.
> We agree that each of the categories should have its own physical
> table, since CDs are different from books, etc.
> We also agree that all categories share similar informations such as a
> price, a name, a purchase date, etc. ... this legitimate the creation
> of a single "products" table. With that table, we will also manage the
> products IDs, since we dont want duplicate among categories.
> Therefore, we have 4 tables to create : Products, Products_Books,
> Products_Toys and Products_CDs.
> The unique ID of a product is hold in the Products table. The
> sub-tables hold category-oriented information. Each sub-tables refer
> to the master table with a many-to-1 FK.
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
> All answers will be appreciate.
> thank you,
> BTG
>|||Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
denormalized design and should be avoided. Are you sure that their
attributes are so different that they would need to be contained in seperate
tables? Doing so will make your SQL queries more complex, and what if the
business decides later that it really needs 4 categories instead? That's a
major revision to both the database model and also the application
programming.
Assuming you still need to go with that design, then you can implement this
by storing the 3 foreign keys in the Products table. Conceptually, I see
this as a relationship with 3 entirely different entities (or so you say)
but with the addition of a rule that a Product can only be related to one of
these entities at a time.
The following foreign key relationships insure that only valid categories
are assigned:
Products.ProductsToysID smallint -> Products_Toys.ProductsToysID
Products.ProductsBooksID smallint -> Products_Books.ProductsBooksID
Products.ProductsCDsID smallint -> Products_CDs.ProductsCDsID
The following check constraint insures that one and only one category is
specified:
(
coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) is not null
)
and
(
coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) =
isnull(ProductsToysID,0) + isnull(ProductsBooksID,0) +
isnull(ProductsCDsID,0)
)
That should handle the data integrity part.
Below is an example of how you would join product category name with a
product list:
select
P.Name as ProductName,
P.Price as ProductPrice,
coalesce(T.Name,B.Name,C.name) as ProductCategoryName
from
Products as P
left join Products_Toys as T
on T.ProductsToysID = P.ProductsToysID
left join Products_Books as B
on B.ProductsBooksID = P.ProductsBooksID
left join Products_CDs as C
on C.ProductsCDsID = P.ProductsCDsID
You may be able to simplify things by making creative use of Views.
<before.the.gods@.gmail.com> wrote in message
news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
> Hello,
> I am in the process of designing a big commercial database for a
> wholesaler.
> Since many months now, I have been asking myself questions about how to
> physically implement a system of tables that would efficiently
> represent the logical situation of many "sub-entities" related to a
> single "master entity".
> Here is a simplified example :
> A company has a catalogue of many products, grouped in 3 Categories :
> toys products, books products and CDs products.
> We agree that each of the categories should have its own physical
> table, since CDs are different from books, etc.
> We also agree that all categories share similar informations such as a
> price, a name, a purchase date, etc. ... this legitimate the creation
> of a single "products" table. With that table, we will also manage the
> products IDs, since we dont want duplicate among categories.
> Therefore, we have 4 tables to create : Products, Products_Books,
> Products_Toys and Products_CDs.
> The unique ID of a product is hold in the Products table. The
> sub-tables hold category-oriented information. Each sub-tables refer
> to the master table with a many-to-1 FK.
> First problem : how can I physically make sure that a product exist in
> only one sub-table? Of course, this is managed at the application
> level ... but how can I put a database-constraint to physically avoid a
> situation where a product entry from the Products table would have both
> a sub-entry in the book AND the toy sub-table.
> Second problem : how can I physically make sure that all products in
> the master table have 1 entry in a sub-table. Or, if you prefer, how
> can I make sure that the FK between the master and the sub-entities is
> a genuine 1-to-many relationship and NOT a 0.1-to-many relationship.
> All answers will be appreciate.
> thank you,
> BTG
>|||> Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
> denormalized design and should be avoided.
No it isn't. And no it shouldn't (always be avoided). See David's post for
the proper structure of a sub-typed relationship. It actually makes things
quite nice, since columns that they have in common are right there for the
searching (name, productType, etc) and then you can get more specific in the
child table (the specific tables.) The Product table would have the primary
key and it would be migrated to the children.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"JT" <someone@.microsoft.com> wrote in message
news:e98WE0QKGHA.516@.TK2MSFTNGP15.phx.gbl...
> Splitting the 3 categories (toys, book, CDs) into 3 seperate tables is a
> denormalized design and should be avoided. Are you sure that their
> attributes are so different that they would need to be contained in
> seperate tables? Doing so will make your SQL queries more complex, and
> what if the business decides later that it really needs 4 categories
> instead? That's a major revision to both the database model and also the
> application programming.
> Assuming you still need to go with that design, then you can implement
> this by storing the 3 foreign keys in the Products table. Conceptually, I
> see this as a relationship with 3 entirely different entities (or so you
> say) but with the addition of a rule that a Product can only be related to
> one of these entities at a time.
> The following foreign key relationships insure that only valid categories
> are assigned:
> Products.ProductsToysID smallint -> Products_Toys.ProductsToysID
> Products.ProductsBooksID smallint ->
> Products_Books.ProductsBooksID
> Products.ProductsCDsID smallint -> Products_CDs.ProductsCDsID
> The following check constraint insures that one and only one category is
> specified:
> (
> coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) is not null
> )
> and
> (
> coalesce(ProductsToysID,ProductsBooksID,
ProductsCDsID) =
> isnull(ProductsToysID,0) + isnull(ProductsBooksID,0) +
> isnull(ProductsCDsID,0)
> )
> That should handle the data integrity part.
> Below is an example of how you would join product category name with a
> product list:
> select
> P.Name as ProductName,
> P.Price as ProductPrice,
> coalesce(T.Name,B.Name,C.name) as ProductCategoryName
> from
> Products as P
> left join Products_Toys as T
> on T.ProductsToysID = P.ProductsToysID
> left join Products_Books as B
> on B.ProductsBooksID = P.ProductsBooksID
> left join Products_CDs as C
> on C.ProductsCDsID = P.ProductsCDsID
> You may be able to simplify things by making creative use of Views.
>
> <before.the.gods@.gmail.com> wrote in message
> news:1138998544.545064.58550@.g14g2000cwa.googlegroups.com...
>