What is the best way to design your tables in cases where field values change?
Example:
CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);
CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);
Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)
is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:
CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);
and have the Purchase table reference the ProductPrice table instead of the products table?
I have used both methods in the past, but I was wanted to get other peoples' take on it.
ThanksBecause price can change for many reasons, I always keep it in the actual transaction row. For example, you might have different prices for a given product based on quantity purchased (for example buying 100 units gets a price break). There might be reasons for different prices based on the customer (one price for wholesale, one for sub-contractors, another price for retail). These differences could be either discreet or cumulative. In short, the price in the inventory table might only be a starting point, the price in the transaction table is the authoritive price for a transaction.
-PatP|||If you want to be able to track historical prices, such as how much a price has changed over time, then you need to add a time dimension to your price table.
But for a financial application such as this there is no substitute to storing the actual price paid in the transation table.|||(which kind of de-normalizes it)
No, it doesn't. :) It's an attribute of the purchase.
The purchase table should have the price paid at time of purchase.
There should be a ProductPrice table that holds the price historically for each price. If you want to avoid duplicating data, you can put the ProductPriceID in the Purchase table so you have the exact price at the time purchase was made.
Showing posts with label design. Show all posts
Showing posts with label design. Show all posts
Thursday, March 22, 2012
Best practices to design datawarehouse for a Fraud Detection/Transaction Monitoring Application
Hi All,
This thread relates to following thread of Architecure forum.
http://forums.microsoft.com/msdn/showpost.aspx?postid=1908857&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=2
Because after reaching at decision that application would require datawarehousing the members suggested that it would be nice if it is posted on datawarehouse forum.
I would appreciate if could go through the mentioned thread that would state application's business requirement.
Now i am continuing the from the thread where we were discussing about SCD.
In our Application customer address is defined in 6 fields (Street,city,State,Country,Pincode,ContactNo) so if we are to maintain 3 type of addresses like Office,Communication,Residence then we have to add 18 fields in our Customer Master dimension table in our dataware house.
Suppose after some time customer office address is changed so what should be done in Customer Master Table at time of synchronization, Should ETL process insert a new record into dimension table with updated address in office address fields or it should update the existing record fields without adding new one.
If for Changing dimensions we are adding a new record then we have to maintain a flag field where status of record would be kept like 'current record' or 'old record' , this would help us to identify the main record.Every time whenever a new customer record is added old record status is changed to 'Old record'.
Should we add new record for changing fields like address,designation,employer,annual income etc or update the existing record only.Because in our analysis these all fields would play major because after each profile updation a risk category is assigned to customer.So we can not afford to overwrite any field information because at any time old information can be asked by management/regulator.
If every time for each field updation a new record is updated then customer master datawarehouse table would become 5 times if each customer record is updated five times during the year.
Suggest us the efficient approach to handle changing dimensions.
Thanks & Regards,
Sameer Gautam
Hi,
You don't have to create a record each time a change happens, just depends if you want to capture the history for that field or not. For example you may want to capture history for change of address, so you would add a new record, but you may not want it for surname, so just update all records for that customer with the new surname.
SCD are a pain to say the least when data warehousing, the principle is nice but coding it is sometimes complicated especially if you try and use snowflakes. And space soon becomes an issue, if you data changes regually. And you are right that if you make a change to a customer 5 times in a year then you will get 5 new records. Although you may code it so it only captures the end of year snapshot, rather than every change.
I would have a look through some of kimballs articles:
http://kimballgroup.com/html/articlesArchitecture/articlesAdvancedDim.html
Good luck
Matt
sqlTuesday, March 20, 2012
Best Practices for Design of Views
Hi, I have been using Views for a while now instead of constructing SQL
statements in the application but one the problems is that as the
applications grow the views need more and more columns, alisaes, and
both Lookup values and Foreign key ID for a range of uses.
At least when constructing SQL SELECT in the application each form or
function has a specific need for columns so you dont aim for any
re-usability.
How it is best to manage this when designing views? Showuld there be
one view only per logical entity that has a lot of columns making it
applicable for a whole range of uses, or individual views, one per use
with its own specific columns. If the latter is used I have a problem
naming and remembering names of all the different views.
An example may help illustrate this:
vwInvoices - This currently contains only the columns needed to diplay
a list of Invoices after doing a search, + any columns used in the
search. But when drilling into a single Invoice from the list it will
need many more columns. While the search tool only needs the foreign
key ID of the user who created it (The search tool users a dropdownn
list of users with IDs behind names) , the full detail screen needs to
show the actual user name from the users table.
Shold this view even be used for for selecting one record? Should a
stored procedure be used instead?
Are there any design principles or best practices anyone ccan share for
these issues.
Thanks.hals_left wrote:
> Hi, I have been using Views for a while now instead of constructing SQL
> statements in the application but one the problems is that as the
> applications grow the views need more and more columns, alisaes, and
> both Lookup values and Foreign key ID for a range of uses.
> At least when constructing SQL SELECT in the application each form or
> function has a specific need for columns so you dont aim for any
> re-usability.
> How it is best to manage this when designing views? Showuld there be
> one view only per logical entity that has a lot of columns making it
> applicable for a whole range of uses, or individual views, one per use
> with its own specific columns. If the latter is used I have a problem
> naming and remembering names of all the different views.
> An example may help illustrate this:
> vwInvoices - This currently contains only the columns needed to diplay
> a list of Invoices after doing a search, + any columns used in the
> search. But when drilling into a single Invoice from the list it will
> need many more columns. While the search tool only needs the foreign
> key ID of the user who created it (The search tool users a dropdownn
> list of users with IDs behind names) , the full detail screen needs to
> show the actual user name from the users table.
> Shold this view even be used for for selecting one record? Should a
> stored procedure be used instead?
> Are there any design principles or best practices anyone ccan share for
> these issues.
> Thanks.
Best practice in most environments is that the application should
access the database only through stored procedures, not views. Procs
are the best method to encapsulate logic, facilitate code reuse,
optimise performance and implement security. Views are useful if you
need to share the same query logic in several procs. For most business
process applications it isn't good practice to access views or tables
directly. See:
http://msdn.microsoft.com/library/d...y/en-us/opti...
http://www.sql-server-performance.c..._procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx
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
--|||Oops. One of the links got scrambled:
http://msdn.microsoft.com/library/d.../>
1a_6x45.asp
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
--|||Thanks David.
If I understand correctly, the best practice to display lines for a
single invoice would therefore be to have a stored procedure that
queries a view , rather than have the application construct a WHERE
clause and query the view directly ?
The procedure would be something like this ?
-- Retrieves the Invoice Lines for 1 invoice
CREATE PROCEDURE [dbo].[getSingleInvoiceLines]
@.InvoiceID Int
AS
SELECT [Description],Quantity,UnitCost, Department,Nominal
FROM dbo.vwInvoiceLines
WHERE Invoice=@.InvoiceID
GO
And used something like this in an application ?
set oRS = objConn.Execute ( "dbo.getSingleInvoiceLines " & intInvoice )
// Or with ADODB.Command / Explicit Parameters etc..
If Not oRS.EOF THen
while not oRS.EOF%>
...|||hals_left wrote:
> Thanks David.
> If I understand correctly, the best practice to display lines for a
> single invoice would therefore be to have a stored procedure that
> queries a view , rather than have the application construct a WHERE
> clause and query the view directly ?
> The procedure would be something like this ?
> -- Retrieves the Invoice Lines for 1 invoice
> CREATE PROCEDURE [dbo].[getSingleInvoiceLines]
> @.InvoiceID Int
> AS
> SELECT [Description],Quantity,UnitCost, Department,Nominal
> FROM dbo.vwInvoiceLines
> WHERE Invoice=@.InvoiceID
> GO
> And used something like this in an application ?
> set oRS = objConn.Execute ( "dbo.getSingleInvoiceLines " & intInvoice )
> // Or with ADODB.Command / Explicit Parameters etc..
> If Not oRS.EOF THen
> while not oRS.EOF%>
> ...
Use the ADO parameters collection rather than construct strings
dynamically. Usually you'll also want to put some error handling in
your procs - in fact that's one of the advantages of procs.
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
--
statements in the application but one the problems is that as the
applications grow the views need more and more columns, alisaes, and
both Lookup values and Foreign key ID for a range of uses.
At least when constructing SQL SELECT in the application each form or
function has a specific need for columns so you dont aim for any
re-usability.
How it is best to manage this when designing views? Showuld there be
one view only per logical entity that has a lot of columns making it
applicable for a whole range of uses, or individual views, one per use
with its own specific columns. If the latter is used I have a problem
naming and remembering names of all the different views.
An example may help illustrate this:
vwInvoices - This currently contains only the columns needed to diplay
a list of Invoices after doing a search, + any columns used in the
search. But when drilling into a single Invoice from the list it will
need many more columns. While the search tool only needs the foreign
key ID of the user who created it (The search tool users a dropdownn
list of users with IDs behind names) , the full detail screen needs to
show the actual user name from the users table.
Shold this view even be used for for selecting one record? Should a
stored procedure be used instead?
Are there any design principles or best practices anyone ccan share for
these issues.
Thanks.hals_left wrote:
> Hi, I have been using Views for a while now instead of constructing SQL
> statements in the application but one the problems is that as the
> applications grow the views need more and more columns, alisaes, and
> both Lookup values and Foreign key ID for a range of uses.
> At least when constructing SQL SELECT in the application each form or
> function has a specific need for columns so you dont aim for any
> re-usability.
> How it is best to manage this when designing views? Showuld there be
> one view only per logical entity that has a lot of columns making it
> applicable for a whole range of uses, or individual views, one per use
> with its own specific columns. If the latter is used I have a problem
> naming and remembering names of all the different views.
> An example may help illustrate this:
> vwInvoices - This currently contains only the columns needed to diplay
> a list of Invoices after doing a search, + any columns used in the
> search. But when drilling into a single Invoice from the list it will
> need many more columns. While the search tool only needs the foreign
> key ID of the user who created it (The search tool users a dropdownn
> list of users with IDs behind names) , the full detail screen needs to
> show the actual user name from the users table.
> Shold this view even be used for for selecting one record? Should a
> stored procedure be used instead?
> Are there any design principles or best practices anyone ccan share for
> these issues.
> Thanks.
Best practice in most environments is that the application should
access the database only through stored procedures, not views. Procs
are the best method to encapsulate logic, facilitate code reuse,
optimise performance and implement security. Views are useful if you
need to share the same query logic in several procs. For most business
process applications it isn't good practice to access views or tables
directly. See:
http://msdn.microsoft.com/library/d...y/en-us/opti...
http://www.sql-server-performance.c..._procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx
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
--|||Oops. One of the links got scrambled:
http://msdn.microsoft.com/library/d.../>
1a_6x45.asp
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
--|||Thanks David.
If I understand correctly, the best practice to display lines for a
single invoice would therefore be to have a stored procedure that
queries a view , rather than have the application construct a WHERE
clause and query the view directly ?
The procedure would be something like this ?
-- Retrieves the Invoice Lines for 1 invoice
CREATE PROCEDURE [dbo].[getSingleInvoiceLines]
@.InvoiceID Int
AS
SELECT [Description],Quantity,UnitCost, Department,Nominal
FROM dbo.vwInvoiceLines
WHERE Invoice=@.InvoiceID
GO
And used something like this in an application ?
set oRS = objConn.Execute ( "dbo.getSingleInvoiceLines " & intInvoice )
// Or with ADODB.Command / Explicit Parameters etc..
If Not oRS.EOF THen
while not oRS.EOF%>
...|||hals_left wrote:
> Thanks David.
> If I understand correctly, the best practice to display lines for a
> single invoice would therefore be to have a stored procedure that
> queries a view , rather than have the application construct a WHERE
> clause and query the view directly ?
> The procedure would be something like this ?
> -- Retrieves the Invoice Lines for 1 invoice
> CREATE PROCEDURE [dbo].[getSingleInvoiceLines]
> @.InvoiceID Int
> AS
> SELECT [Description],Quantity,UnitCost, Department,Nominal
> FROM dbo.vwInvoiceLines
> WHERE Invoice=@.InvoiceID
> GO
> And used something like this in an application ?
> set oRS = objConn.Execute ( "dbo.getSingleInvoiceLines " & intInvoice )
> // Or with ADODB.Command / Explicit Parameters etc..
> If Not oRS.EOF THen
> while not oRS.EOF%>
> ...
Use the ADO parameters collection rather than construct strings
dynamically. Usually you'll also want to put some error handling in
your procs - in fact that's one of the advantages of procs.
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
--
Saturday, February 25, 2012
Best Design Practice?
I'm building a database that has maybe four unique tables Student,
Advertiser, Employee, maybe Account. Three of the four table (Student,
Advertiser, Employee) have something in common in which they all contain
fields such as emailAddress, password, role, isAccountActive, etc. which
allow them to access their respected data. However, is it best practice to
build a fourth table which contain Account information or should I just
include that information in their respected tables?
My thinking is that if you have a fourth table such as Account then you can
manage all accounts (Student, Advertiser, Employee) from one table, but as
the database gets more in-depth you have to build more and more complex
stored procedure to do simply task such as update, delete, select, etc.i would probably combine all 4 tables into one because of the columns the first three have in common
it really depends on how many columns they don't have in common|||try reading this.
Database Design for Mere Mortals (http://www.amazon.com/gp/product/customer-reviews/0201752840/102-7659258-1375352?_encoding=UTF8&me=ATVPDKIKX0DER&s=books)|||in addition, i truly beleive that the data model is entirely indicative of the business model or in other words, the data model already exists it's your job to discover it.
the rules are defined as a dictum for all to follow. some follow them more strictly than others and some bend the rules to accomodate performance and simplicity requirements.
as a designer, you should know the rules before you can intelligently break them.|||the rules are defined as a dictum for all to follow. some follow them more strictly than others and some bend the rules to accomodate performance and simplicity requirements.you'll love this discussion, then --
http://www.kottke.org/04/10/normalized-data
by the way, i trust that remark about making an attempt to read a web page on amazon was not directed at me :)|||you'll love this discussion, then --
http://www.kottke.org/04/10/normalized-data
by the way, i trust that remark about making an attempt to read a web page on amazon was not directed at me :)
what the hell are you talking about?
by the way i read your link and that is just another example of a application developer assuming that he has the chops for db work. just because you can type create table create view and creat proc , in no way makes you a dba it just makes us all look bad when the shiat hits the fan and said app developer cant fix it.
I hate this subject so much that just a 5 minute exposure to this article has totaly pissed me off.
"ooooooohhh i hate that rabbit"|||what the hell are you talking about?i was talking about your comment in post #3 which immediately followed my post #2 -- i assume your rather arrogant comment "try reading this" wasn't directed at me
you should read that kottke article a little more closely, it is the comments to the article that are all the fun, kottke himself is not a dba, he was just raising the issue
if the name kottke means nothing to you, that's fine, i guess you haven't been around the web much
the name cal henderson may not mean anything either, but he is the one who developed the back end for flickr, and you really should read his powerpoint presentation, flickr is an amazing project and i personally don't have th chops to do anything remotely like that
maybe you do, though
:)|||R2D2.
against my better judgement, I will issue the following statement.
my initial post was directed to the guy who started this thread.
my follow up post was a kind of postscript to the previous post and once again was directed to the guy who started this thread
at no point were you even on my radar.|||I just want to thank you guys for the help. I have now made a sound decision on my database design.|||...and off to the holy quest, to seek the holy model...
...and sometimes our heated discussions remind me of this opening scene (http://www.mwscomp.com/movies/grail/grail-01.htm)...|||at no point were you even on my radar.oh, that's so adult of you, rupert|||Neutral corners, please!
Advertiser, Employee, maybe Account. Three of the four table (Student,
Advertiser, Employee) have something in common in which they all contain
fields such as emailAddress, password, role, isAccountActive, etc. which
allow them to access their respected data. However, is it best practice to
build a fourth table which contain Account information or should I just
include that information in their respected tables?
My thinking is that if you have a fourth table such as Account then you can
manage all accounts (Student, Advertiser, Employee) from one table, but as
the database gets more in-depth you have to build more and more complex
stored procedure to do simply task such as update, delete, select, etc.i would probably combine all 4 tables into one because of the columns the first three have in common
it really depends on how many columns they don't have in common|||try reading this.
Database Design for Mere Mortals (http://www.amazon.com/gp/product/customer-reviews/0201752840/102-7659258-1375352?_encoding=UTF8&me=ATVPDKIKX0DER&s=books)|||in addition, i truly beleive that the data model is entirely indicative of the business model or in other words, the data model already exists it's your job to discover it.
the rules are defined as a dictum for all to follow. some follow them more strictly than others and some bend the rules to accomodate performance and simplicity requirements.
as a designer, you should know the rules before you can intelligently break them.|||the rules are defined as a dictum for all to follow. some follow them more strictly than others and some bend the rules to accomodate performance and simplicity requirements.you'll love this discussion, then --
http://www.kottke.org/04/10/normalized-data
by the way, i trust that remark about making an attempt to read a web page on amazon was not directed at me :)|||you'll love this discussion, then --
http://www.kottke.org/04/10/normalized-data
by the way, i trust that remark about making an attempt to read a web page on amazon was not directed at me :)
what the hell are you talking about?
by the way i read your link and that is just another example of a application developer assuming that he has the chops for db work. just because you can type create table create view and creat proc , in no way makes you a dba it just makes us all look bad when the shiat hits the fan and said app developer cant fix it.
I hate this subject so much that just a 5 minute exposure to this article has totaly pissed me off.
"ooooooohhh i hate that rabbit"|||what the hell are you talking about?i was talking about your comment in post #3 which immediately followed my post #2 -- i assume your rather arrogant comment "try reading this" wasn't directed at me
you should read that kottke article a little more closely, it is the comments to the article that are all the fun, kottke himself is not a dba, he was just raising the issue
if the name kottke means nothing to you, that's fine, i guess you haven't been around the web much
the name cal henderson may not mean anything either, but he is the one who developed the back end for flickr, and you really should read his powerpoint presentation, flickr is an amazing project and i personally don't have th chops to do anything remotely like that
maybe you do, though
:)|||R2D2.
against my better judgement, I will issue the following statement.
my initial post was directed to the guy who started this thread.
my follow up post was a kind of postscript to the previous post and once again was directed to the guy who started this thread
at no point were you even on my radar.|||I just want to thank you guys for the help. I have now made a sound decision on my database design.|||...and off to the holy quest, to seek the holy model...
...and sometimes our heated discussions remind me of this opening scene (http://www.mwscomp.com/movies/grail/grail-01.htm)...|||at no point were you even on my radar.oh, that's so adult of you, rupert|||Neutral corners, please!
Best design method to allow for "dynamic" records
First, a quick overview of my project. I'm designing a vehicle
tracking system that takes data from multiple types of GPS devices,
stores the data in a common database, and allows the user to view
device locations in real time or create reports on previous activity.
Currently we're only using one type of device, but I'm trying to
futureproof the app so I don't have to redesign it down the road.
Since the devices have different capabilities, I'm trying to come up
with the best method to store their data in a common format. For
example, say I have two devices, DeviceA and DeviceB. Both can report
their latitude, longitude, speed, heading, and a timestamp. DeviceA
can also report an odometer value, whether or not it has a GPS fix,
and telematics data. DeviceB cannot report those values. Along with
this data, every record will be tagged with address information on the
server side. Down the road, we may have DeviceC, DeviceD, etc with
new capabilities.
Now, the question is, how can I store all of this information in a way
that is simple to search/order/etc that is also "dynamic"? I've
thought about using three tables...one would contain "basic" record
info...latitude, longitude, speed, heading, timestamp, and a reason
code for why the record was sent (ignition on/off, start/stop, etc).
A second table would contain address information (street number,
street name, city, state, ZIP) and would be linked back to the basic
table. The third table would contain a single field with an XML
fragment detailing the rest of the information for that record
(odometer value, GPS fix status, telematics, etc).
Problem is, I can't find any method to allow me to search through XML
contained in a column other than a full text search. I also have the
problem of creating a result set containing all of the dynamic columns
(or selected ones only) to be returned to my ASP.NET application for
reporting.
Other methods I have thought about...storing all "extra" info in a
huge table with columns for each value. This would result in a lot of
wasted space (NULL values everywhere for devices that don't support
those features) and new columns would have to be added each time a new
device is supported (if new features are provided). Yet another
method...store values as key/value pairs in a separate table. This
would be rediculously slow though...I would have to generate some
heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
values I need and dump them into a result set.
The data volume will be large (50K-60K records per day) and reporting
needs to be fairly responsive (web based reporting system...generate a
result set from SQL Server, typically using a date/time range, with
the required fields and pass back to data access layer for final
processing). So...out of the three methods I've thought about...any
comments or thoughts about which would be the best way to go? Are
there any other methods I should take into consideration? I know SQL
Server 2005 is supposed to have much improved XML support if I go that
route, but that's not an option at this point...I'm stuck with using
2000 for now.
Thanks for any help you can offer...it will be greatly appreciated!
Why dont you have a table like this for the extra info
(Vehicle ID, Capability ID, Value)
Vehicle ID + Capability ID will be the primary key. You can avoid the NULLs this way as you have rows for only those capabilities the vehicle has. If you think you dont add vehicles often to the system, then you can remove the VehicleID from the table and
create a table for every Vehicle with (CapabilityID and Value) as columns.
Is this a possible option?
Chandra
"Jeff L." wrote:
> First, a quick overview of my project. I'm designing a vehicle
> tracking system that takes data from multiple types of GPS devices,
> stores the data in a common database, and allows the user to view
> device locations in real time or create reports on previous activity.
> Currently we're only using one type of device, but I'm trying to
> futureproof the app so I don't have to redesign it down the road.
> Since the devices have different capabilities, I'm trying to come up
> with the best method to store their data in a common format. For
> example, say I have two devices, DeviceA and DeviceB. Both can report
> their latitude, longitude, speed, heading, and a timestamp. DeviceA
> can also report an odometer value, whether or not it has a GPS fix,
> and telematics data. DeviceB cannot report those values. Along with
> this data, every record will be tagged with address information on the
> server side. Down the road, we may have DeviceC, DeviceD, etc with
> new capabilities.
> Now, the question is, how can I store all of this information in a way
> that is simple to search/order/etc that is also "dynamic"? I've
> thought about using three tables...one would contain "basic" record
> info...latitude, longitude, speed, heading, timestamp, and a reason
> code for why the record was sent (ignition on/off, start/stop, etc).
> A second table would contain address information (street number,
> street name, city, state, ZIP) and would be linked back to the basic
> table. The third table would contain a single field with an XML
> fragment detailing the rest of the information for that record
> (odometer value, GPS fix status, telematics, etc).
> Problem is, I can't find any method to allow me to search through XML
> contained in a column other than a full text search. I also have the
> problem of creating a result set containing all of the dynamic columns
> (or selected ones only) to be returned to my ASP.NET application for
> reporting.
> Other methods I have thought about...storing all "extra" info in a
> huge table with columns for each value. This would result in a lot of
> wasted space (NULL values everywhere for devices that don't support
> those features) and new columns would have to be added each time a new
> device is supported (if new features are provided). Yet another
> method...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
> Thanks for any help you can offer...it will be greatly appreciated!
>
|||Why dont you have a table like this for the extra info
(Vehicle ID, Capability ID, Value)
Vehicle ID + Capability ID will be the primary key. You can avoid the NULLs this way as you have rows for only those capabilities the vehicle has. If you think you dont add vehicles often to the system, then you can remove the VehicleID from the table and
create a table for every Vehicle with (CapabilityID and Value) as columns.
Is this a possible option?
Chandra
"Jeff L." wrote:
> First, a quick overview of my project. I'm designing a vehicle
> tracking system that takes data from multiple types of GPS devices,
> stores the data in a common database, and allows the user to view
> device locations in real time or create reports on previous activity.
> Currently we're only using one type of device, but I'm trying to
> futureproof the app so I don't have to redesign it down the road.
> Since the devices have different capabilities, I'm trying to come up
> with the best method to store their data in a common format. For
> example, say I have two devices, DeviceA and DeviceB. Both can report
> their latitude, longitude, speed, heading, and a timestamp. DeviceA
> can also report an odometer value, whether or not it has a GPS fix,
> and telematics data. DeviceB cannot report those values. Along with
> this data, every record will be tagged with address information on the
> server side. Down the road, we may have DeviceC, DeviceD, etc with
> new capabilities.
> Now, the question is, how can I store all of this information in a way
> that is simple to search/order/etc that is also "dynamic"? I've
> thought about using three tables...one would contain "basic" record
> info...latitude, longitude, speed, heading, timestamp, and a reason
> code for why the record was sent (ignition on/off, start/stop, etc).
> A second table would contain address information (street number,
> street name, city, state, ZIP) and would be linked back to the basic
> table. The third table would contain a single field with an XML
> fragment detailing the rest of the information for that record
> (odometer value, GPS fix status, telematics, etc).
> Problem is, I can't find any method to allow me to search through XML
> contained in a column other than a full text search. I also have the
> problem of creating a result set containing all of the dynamic columns
> (or selected ones only) to be returned to my ASP.NET application for
> reporting.
> Other methods I have thought about...storing all "extra" info in a
> huge table with columns for each value. This would result in a lot of
> wasted space (NULL values everywhere for devices that don't support
> those features) and new columns would have to be added each time a new
> device is supported (if new features are provided). Yet another
> method...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
> Thanks for any help you can offer...it will be greatly appreciated!
>
|||Hi Jeff,
SQL 2005 in effect adds the ability to XQuery the data on a column; there is
the "XML" column type that allows this. Also the performance is quite good
since (from what I understand) data is optimized and indexed based on the
XSD information given when defining this field.
Of course this approach is not an option since Yukon is still many months
away.
See below on the poinst I suggest you to follow... and a mid-way solution
that can help!
Ciao,
Adriano
>...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
Yes this kind of normalization is very good because you don't rely on actual
fields to store information, thus reducing space wasting.
Performance-speacking: SQL Server 2000 has a great set of features you can
use to improve querying speed.
1) Indexed views: You can perform aggregations on this table using Indexed
Views in order to have real-time view of your data in a "de-normalized" and
summarized way, where necessary.
2) Mantain only last-month data here so you have last-month queries quite
fast; move the oldest ones in a parallel "history "table. Report this table
only when explicitly requested by the user.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
Another solution?
Build many tables, one for each "device type".. .where you can store "extra"
information without wasting space.
> Thanks for any help you can offer...it will be greatly appreciated!
tracking system that takes data from multiple types of GPS devices,
stores the data in a common database, and allows the user to view
device locations in real time or create reports on previous activity.
Currently we're only using one type of device, but I'm trying to
futureproof the app so I don't have to redesign it down the road.
Since the devices have different capabilities, I'm trying to come up
with the best method to store their data in a common format. For
example, say I have two devices, DeviceA and DeviceB. Both can report
their latitude, longitude, speed, heading, and a timestamp. DeviceA
can also report an odometer value, whether or not it has a GPS fix,
and telematics data. DeviceB cannot report those values. Along with
this data, every record will be tagged with address information on the
server side. Down the road, we may have DeviceC, DeviceD, etc with
new capabilities.
Now, the question is, how can I store all of this information in a way
that is simple to search/order/etc that is also "dynamic"? I've
thought about using three tables...one would contain "basic" record
info...latitude, longitude, speed, heading, timestamp, and a reason
code for why the record was sent (ignition on/off, start/stop, etc).
A second table would contain address information (street number,
street name, city, state, ZIP) and would be linked back to the basic
table. The third table would contain a single field with an XML
fragment detailing the rest of the information for that record
(odometer value, GPS fix status, telematics, etc).
Problem is, I can't find any method to allow me to search through XML
contained in a column other than a full text search. I also have the
problem of creating a result set containing all of the dynamic columns
(or selected ones only) to be returned to my ASP.NET application for
reporting.
Other methods I have thought about...storing all "extra" info in a
huge table with columns for each value. This would result in a lot of
wasted space (NULL values everywhere for devices that don't support
those features) and new columns would have to be added each time a new
device is supported (if new features are provided). Yet another
method...store values as key/value pairs in a separate table. This
would be rediculously slow though...I would have to generate some
heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
values I need and dump them into a result set.
The data volume will be large (50K-60K records per day) and reporting
needs to be fairly responsive (web based reporting system...generate a
result set from SQL Server, typically using a date/time range, with
the required fields and pass back to data access layer for final
processing). So...out of the three methods I've thought about...any
comments or thoughts about which would be the best way to go? Are
there any other methods I should take into consideration? I know SQL
Server 2005 is supposed to have much improved XML support if I go that
route, but that's not an option at this point...I'm stuck with using
2000 for now.
Thanks for any help you can offer...it will be greatly appreciated!
Why dont you have a table like this for the extra info
(Vehicle ID, Capability ID, Value)
Vehicle ID + Capability ID will be the primary key. You can avoid the NULLs this way as you have rows for only those capabilities the vehicle has. If you think you dont add vehicles often to the system, then you can remove the VehicleID from the table and
create a table for every Vehicle with (CapabilityID and Value) as columns.
Is this a possible option?
Chandra
"Jeff L." wrote:
> First, a quick overview of my project. I'm designing a vehicle
> tracking system that takes data from multiple types of GPS devices,
> stores the data in a common database, and allows the user to view
> device locations in real time or create reports on previous activity.
> Currently we're only using one type of device, but I'm trying to
> futureproof the app so I don't have to redesign it down the road.
> Since the devices have different capabilities, I'm trying to come up
> with the best method to store their data in a common format. For
> example, say I have two devices, DeviceA and DeviceB. Both can report
> their latitude, longitude, speed, heading, and a timestamp. DeviceA
> can also report an odometer value, whether or not it has a GPS fix,
> and telematics data. DeviceB cannot report those values. Along with
> this data, every record will be tagged with address information on the
> server side. Down the road, we may have DeviceC, DeviceD, etc with
> new capabilities.
> Now, the question is, how can I store all of this information in a way
> that is simple to search/order/etc that is also "dynamic"? I've
> thought about using three tables...one would contain "basic" record
> info...latitude, longitude, speed, heading, timestamp, and a reason
> code for why the record was sent (ignition on/off, start/stop, etc).
> A second table would contain address information (street number,
> street name, city, state, ZIP) and would be linked back to the basic
> table. The third table would contain a single field with an XML
> fragment detailing the rest of the information for that record
> (odometer value, GPS fix status, telematics, etc).
> Problem is, I can't find any method to allow me to search through XML
> contained in a column other than a full text search. I also have the
> problem of creating a result set containing all of the dynamic columns
> (or selected ones only) to be returned to my ASP.NET application for
> reporting.
> Other methods I have thought about...storing all "extra" info in a
> huge table with columns for each value. This would result in a lot of
> wasted space (NULL values everywhere for devices that don't support
> those features) and new columns would have to be added each time a new
> device is supported (if new features are provided). Yet another
> method...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
> Thanks for any help you can offer...it will be greatly appreciated!
>
|||Why dont you have a table like this for the extra info
(Vehicle ID, Capability ID, Value)
Vehicle ID + Capability ID will be the primary key. You can avoid the NULLs this way as you have rows for only those capabilities the vehicle has. If you think you dont add vehicles often to the system, then you can remove the VehicleID from the table and
create a table for every Vehicle with (CapabilityID and Value) as columns.
Is this a possible option?
Chandra
"Jeff L." wrote:
> First, a quick overview of my project. I'm designing a vehicle
> tracking system that takes data from multiple types of GPS devices,
> stores the data in a common database, and allows the user to view
> device locations in real time or create reports on previous activity.
> Currently we're only using one type of device, but I'm trying to
> futureproof the app so I don't have to redesign it down the road.
> Since the devices have different capabilities, I'm trying to come up
> with the best method to store their data in a common format. For
> example, say I have two devices, DeviceA and DeviceB. Both can report
> their latitude, longitude, speed, heading, and a timestamp. DeviceA
> can also report an odometer value, whether or not it has a GPS fix,
> and telematics data. DeviceB cannot report those values. Along with
> this data, every record will be tagged with address information on the
> server side. Down the road, we may have DeviceC, DeviceD, etc with
> new capabilities.
> Now, the question is, how can I store all of this information in a way
> that is simple to search/order/etc that is also "dynamic"? I've
> thought about using three tables...one would contain "basic" record
> info...latitude, longitude, speed, heading, timestamp, and a reason
> code for why the record was sent (ignition on/off, start/stop, etc).
> A second table would contain address information (street number,
> street name, city, state, ZIP) and would be linked back to the basic
> table. The third table would contain a single field with an XML
> fragment detailing the rest of the information for that record
> (odometer value, GPS fix status, telematics, etc).
> Problem is, I can't find any method to allow me to search through XML
> contained in a column other than a full text search. I also have the
> problem of creating a result set containing all of the dynamic columns
> (or selected ones only) to be returned to my ASP.NET application for
> reporting.
> Other methods I have thought about...storing all "extra" info in a
> huge table with columns for each value. This would result in a lot of
> wasted space (NULL values everywhere for devices that don't support
> those features) and new columns would have to be added each time a new
> device is supported (if new features are provided). Yet another
> method...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
> Thanks for any help you can offer...it will be greatly appreciated!
>
|||Hi Jeff,
SQL 2005 in effect adds the ability to XQuery the data on a column; there is
the "XML" column type that allows this. Also the performance is quite good
since (from what I understand) data is optimized and indexed based on the
XSD information given when defining this field.
Of course this approach is not an option since Yukon is still many months
away.
See below on the poinst I suggest you to follow... and a mid-way solution
that can help!
Ciao,
Adriano
>...store values as key/value pairs in a separate table. This
> would be rediculously slow though...I would have to generate some
> heavy-duty dynamic SQL (crosstab query, basically) to pull all of the
> values I need and dump them into a result set.
Yes this kind of normalization is very good because you don't rely on actual
fields to store information, thus reducing space wasting.
Performance-speacking: SQL Server 2000 has a great set of features you can
use to improve querying speed.
1) Indexed views: You can perform aggregations on this table using Indexed
Views in order to have real-time view of your data in a "de-normalized" and
summarized way, where necessary.
2) Mantain only last-month data here so you have last-month queries quite
fast; move the oldest ones in a parallel "history "table. Report this table
only when explicitly requested by the user.
> The data volume will be large (50K-60K records per day) and reporting
> needs to be fairly responsive (web based reporting system...generate a
> result set from SQL Server, typically using a date/time range, with
> the required fields and pass back to data access layer for final
> processing). So...out of the three methods I've thought about...any
> comments or thoughts about which would be the best way to go? Are
> there any other methods I should take into consideration? I know SQL
> Server 2005 is supposed to have much improved XML support if I go that
> route, but that's not an option at this point...I'm stuck with using
> 2000 for now.
Another solution?
Build many tables, one for each "device type".. .where you can store "extra"
information without wasting space.
> Thanks for any help you can offer...it will be greatly appreciated!
Best Design for reports over time
Hi everyone,
I am designing a reporting system for the internet using Reporting
Services. Is there a way to access the historical snapshots
programmatically? We are displaying the reports in the browser by
linking to them in the url, but that brings up the current month's
report. We want to be able to run the report monthly(different data)
and generate a snapshot, then link to the snapshots from the custom
ASP.Net application.
Can we do this? Is is there a better way?
Thanks in advance,
ShawnJust render a history snapshot and look at the generated URL. The URL will
look like this:
http://ServerName/Reports/Pages/Report.aspx?ItemPath=%SomeReport&HistoryID=2005-05-11T22:25:40
The HistoryID identifies the history snapshot in UTC time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:7B7A9AA3-2EBB-45D2-85B2-AD58FE3A5A09@.microsoft.com...
> Hi everyone,
> I am designing a reporting system for the internet using Reporting
> Services. Is there a way to access the historical snapshots
> programmatically? We are displaying the reports in the browser by
> linking to them in the url, but that brings up the current month's
> report. We want to be able to run the report monthly(different data)
> and generate a snapshot, then link to the snapshots from the custom
> ASP.Net application.
>
> Can we do this? Is is there a better way?
>
> Thanks in advance,
> Shawn
>
>
I am designing a reporting system for the internet using Reporting
Services. Is there a way to access the historical snapshots
programmatically? We are displaying the reports in the browser by
linking to them in the url, but that brings up the current month's
report. We want to be able to run the report monthly(different data)
and generate a snapshot, then link to the snapshots from the custom
ASP.Net application.
Can we do this? Is is there a better way?
Thanks in advance,
ShawnJust render a history snapshot and look at the generated URL. The URL will
look like this:
http://ServerName/Reports/Pages/Report.aspx?ItemPath=%SomeReport&HistoryID=2005-05-11T22:25:40
The HistoryID identifies the history snapshot in UTC time.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sysdesigner" <sysdesigner@.discussions.microsoft.com> wrote in message
news:7B7A9AA3-2EBB-45D2-85B2-AD58FE3A5A09@.microsoft.com...
> Hi everyone,
> I am designing a reporting system for the internet using Reporting
> Services. Is there a way to access the historical snapshots
> programmatically? We are displaying the reports in the browser by
> linking to them in the url, but that brings up the current month's
> report. We want to be able to run the report monthly(different data)
> and generate a snapshot, then link to the snapshots from the custom
> ASP.Net application.
>
> Can we do this? Is is there a better way?
>
> Thanks in advance,
> Shawn
>
>
Best design for edit tracking?
Hey all,
This is a general question. Here's my scenario: We have a legacy
database. The core table within contains almost 4 million records in
SQL Server. Currently we have a web front end which allows uses to
search through the database for the information they need.
What the client wants is a web front end which allows some users to
edit the core table above. No problem. What the client also wants is
for the non-editable web front end (used for research) to display (via
colored text) which records have been edited. Example:
1.) Jimmy edits records x, y, and z in coretable1 using the editing
web app.
2.) Jane comes along and uses the research web app to hunt down some
records. She views records s through z. In her view she notices that
certain cells in rows x, y, and z are colored red. This tells her that
Jimmy has edited those specific fields in those specific rows.
My question is: what is the most efficient way to track these column
specific edits so that my web app can display them? This may seem like
a web dev question, but the reality is that my web apps have to
interact with SQL Server so if anyone has any input, I'd love to hear
it! Thanks.OK, the quickest solution to this goest something like this...
Add two rows to your core table, the first is a DateTime with a Default
constraint of GetDate(). The second column is used to identify who made the
change.
Next when update one of the rows, you need to simply include the identifier
of the person who made the change.
Next when you read the table you need to read and compare two rows. The
most recent row, and the second most recent row. This provides the
information about what changed - as a freebie you'll gain access to the old
version of the row.
Another approach would be to have two tables. The first is the core table
as it is now. The second table contains a flag for each column in your core
table, the DateTime column (as above) and the user identifier column (as
above). This time when you edit the row, you also place a new row into this
table settings the flags for the columns which were altered. Don't forget
to include a foreign key back to your core table. When you select the row
from the core table you can also join on your change map, selecting the
max(datetime) and this will tell what columns were changed in the last edit.
But it won't tell you the values that they were before. There is a big
advantage in this method as your core table doesn't need to be altered.
Regards
Colin Dawson
www.cjdawson.com
"roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
> Hey all,
> This is a general question. Here's my scenario: We have a legacy
> database. The core table within contains almost 4 million records in
> SQL Server. Currently we have a web front end which allows uses to
> search through the database for the information they need.
> What the client wants is a web front end which allows some users to
> edit the core table above. No problem. What the client also wants is
> for the non-editable web front end (used for research) to display (via
> colored text) which records have been edited. Example:
> 1.) Jimmy edits records x, y, and z in coretable1 using the editing
> web app.
> 2.) Jane comes along and uses the research web app to hunt down some
> records. She views records s through z. In her view she notices that
> certain cells in rows x, y, and z are colored red. This tells her that
> Jimmy has edited those specific fields in those specific rows.
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
>|||Colin
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
I think you menat "add to columns", and it is worth mentioning that with
this solutuin you will have to write a trigget on that table in order to
track changes
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
> OK, the quickest solution to this goest something like this...
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
> Next when update one of the rows, you need to simply include the
> identifier of the person who made the change.
> Next when you read the table you need to read and compare two rows. The
> most recent row, and the second most recent row. This provides the
> information about what changed - as a freebie you'll gain access to the
> old version of the row.
> Another approach would be to have two tables. The first is the core table
> as it is now. The second table contains a flag for each column in your
> core table, the DateTime column (as above) and the user identifier column
> (as above). This time when you edit the row, you also place a new row
> into this table settings the flags for the columns which were altered.
> Don't forget to include a foreign key back to your core table. When you
> select the row from the core table you can also join on your change map,
> selecting the max(datetime) and this will tell what columns were changed
> in the last edit. But it won't tell you the values that they were before.
> There is a big advantage in this method as your core table doesn't need to
> be altered.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
> news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
>|||oops, I did mean columns yes.
A trigger won't really be able to help as you'll need to supply extra
information than what is stored in the original table. It would be better
to use a Stored procedure and directly enter the data into the new columns.
Of course, the exception to this is that if the application connects to SQL
using seperate usernames, it is possible to use the @.@.User in a trigger to
accomplish the same result. With the applications that my company creates,
this is not possible as they alway connect with the same user (connection
pooling)
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uSloXSxdGHA.4108@.TK2MSFTNGP03.phx.gbl...
> Colin
> I think you menat "add to columns", and it is worth mentioning that with
> this solutuin you will have to write a trigget on that table in order to
> track changes
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
>|||On 13 May 2006 08:30:37 -0700, roy.@.nderson@.gm@.il.com wrote:
>Hey all,
>This is a general question. Here's my scenario: We have a legacy
>database. The core table within contains almost 4 million records in
>SQL Server. Currently we have a web front end which allows uses to
>search through the database for the information they need.
>What the client wants is a web front end which allows some users to
>edit the core table above. No problem. What the client also wants is
>for the non-editable web front end (used for research) to display (via
>colored text) which records have been edited. Example:
>1.) Jimmy edits records x, y, and z in coretable1 using the editing
>web app.
>2.) Jane comes along and uses the research web app to hunt down some
>records. She views records s through z. In her view she notices that
>certain cells in rows x, y, and z are colored red. This tells her that
>Jimmy has edited those specific fields in those specific rows.
>My question is: what is the most efficient way to track these column
>specific edits so that my web app can display them? This may seem like
>a web dev question, but the reality is that my web apps have to
>interact with SQL Server so if anyone has any input, I'd love to hear
>it! Thanks.
Hi Roy,
This is impossible to answer, because the requirements are incomplete.
For instance, what happens if Jimmy edits rows x, y, and z (as in your
example), then Joan edits rows w and x, then Jimmy edits z another time
and then Jane views rows s through z. What columns in what rows have to
be marked as "changed"?
Another question - what if Jimmy edits rows x, y, and z; then nothin
happens for a long time. After a year, Jane looks at rows s through z.
Should Jimmy's changes still be marked?
Hugo Kornelis, SQL Server MVP|||>
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
There are several ways to accomplish that. Do you want your system to
be optimized for retrieval of the current version but support
occasional drill down into editing history. Or do you want to optimize
retrieving history of edits and are ready to pay the price of slowing
down retrieval of current version?
This is a general question. Here's my scenario: We have a legacy
database. The core table within contains almost 4 million records in
SQL Server. Currently we have a web front end which allows uses to
search through the database for the information they need.
What the client wants is a web front end which allows some users to
edit the core table above. No problem. What the client also wants is
for the non-editable web front end (used for research) to display (via
colored text) which records have been edited. Example:
1.) Jimmy edits records x, y, and z in coretable1 using the editing
web app.
2.) Jane comes along and uses the research web app to hunt down some
records. She views records s through z. In her view she notices that
certain cells in rows x, y, and z are colored red. This tells her that
Jimmy has edited those specific fields in those specific rows.
My question is: what is the most efficient way to track these column
specific edits so that my web app can display them? This may seem like
a web dev question, but the reality is that my web apps have to
interact with SQL Server so if anyone has any input, I'd love to hear
it! Thanks.OK, the quickest solution to this goest something like this...
Add two rows to your core table, the first is a DateTime with a Default
constraint of GetDate(). The second column is used to identify who made the
change.
Next when update one of the rows, you need to simply include the identifier
of the person who made the change.
Next when you read the table you need to read and compare two rows. The
most recent row, and the second most recent row. This provides the
information about what changed - as a freebie you'll gain access to the old
version of the row.
Another approach would be to have two tables. The first is the core table
as it is now. The second table contains a flag for each column in your core
table, the DateTime column (as above) and the user identifier column (as
above). This time when you edit the row, you also place a new row into this
table settings the flags for the columns which were altered. Don't forget
to include a foreign key back to your core table. When you select the row
from the core table you can also join on your change map, selecting the
max(datetime) and this will tell what columns were changed in the last edit.
But it won't tell you the values that they were before. There is a big
advantage in this method as your core table doesn't need to be altered.
Regards
Colin Dawson
www.cjdawson.com
"roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
> Hey all,
> This is a general question. Here's my scenario: We have a legacy
> database. The core table within contains almost 4 million records in
> SQL Server. Currently we have a web front end which allows uses to
> search through the database for the information they need.
> What the client wants is a web front end which allows some users to
> edit the core table above. No problem. What the client also wants is
> for the non-editable web front end (used for research) to display (via
> colored text) which records have been edited. Example:
> 1.) Jimmy edits records x, y, and z in coretable1 using the editing
> web app.
> 2.) Jane comes along and uses the research web app to hunt down some
> records. She views records s through z. In her view she notices that
> certain cells in rows x, y, and z are colored red. This tells her that
> Jimmy has edited those specific fields in those specific rows.
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
>|||Colin
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
I think you menat "add to columns", and it is worth mentioning that with
this solutuin you will have to write a trigget on that table in order to
track changes
"Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
> OK, the quickest solution to this goest something like this...
> Add two rows to your core table, the first is a DateTime with a Default
> constraint of GetDate(). The second column is used to identify who made
> the change.
> Next when update one of the rows, you need to simply include the
> identifier of the person who made the change.
> Next when you read the table you need to read and compare two rows. The
> most recent row, and the second most recent row. This provides the
> information about what changed - as a freebie you'll gain access to the
> old version of the row.
> Another approach would be to have two tables. The first is the core table
> as it is now. The second table contains a flag for each column in your
> core table, the DateTime column (as above) and the user identifier column
> (as above). This time when you edit the row, you also place a new row
> into this table settings the flags for the columns which were altered.
> Don't forget to include a foreign key back to your core table. When you
> select the row from the core table you can also join on your change map,
> selecting the max(datetime) and this will tell what columns were changed
> in the last edit. But it won't tell you the values that they were before.
> There is a big advantage in this method as your core table doesn't need to
> be altered.
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "roy.@.nderson@.gm@.il.com" <roy.anderson@.gmail.com> wrote in message
> news:1147534237.719954.218790@.j33g2000cwa.googlegroups.com...
>|||oops, I did mean columns yes.
A trigger won't really be able to help as you'll need to supply extra
information than what is stored in the original table. It would be better
to use a Stored procedure and directly enter the data into the new columns.
Of course, the exception to this is that if the application connects to SQL
using seperate usernames, it is possible to use the @.@.User in a trigger to
accomplish the same result. With the applications that my company creates,
this is not possible as they alway connect with the same user (connection
pooling)
Regards
Colin Dawson
www.cjdawson.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uSloXSxdGHA.4108@.TK2MSFTNGP03.phx.gbl...
> Colin
> I think you menat "add to columns", and it is worth mentioning that with
> this solutuin you will have to write a trigget on that table in order to
> track changes
>
> "Colin Dawson" <newsgroups@.cjdawson.com> wrote in message
> news:yRn9g.68620$wl.14113@.text.news.blueyonder.co.uk...
>|||On 13 May 2006 08:30:37 -0700, roy.@.nderson@.gm@.il.com wrote:
>Hey all,
>This is a general question. Here's my scenario: We have a legacy
>database. The core table within contains almost 4 million records in
>SQL Server. Currently we have a web front end which allows uses to
>search through the database for the information they need.
>What the client wants is a web front end which allows some users to
>edit the core table above. No problem. What the client also wants is
>for the non-editable web front end (used for research) to display (via
>colored text) which records have been edited. Example:
>1.) Jimmy edits records x, y, and z in coretable1 using the editing
>web app.
>2.) Jane comes along and uses the research web app to hunt down some
>records. She views records s through z. In her view she notices that
>certain cells in rows x, y, and z are colored red. This tells her that
>Jimmy has edited those specific fields in those specific rows.
>My question is: what is the most efficient way to track these column
>specific edits so that my web app can display them? This may seem like
>a web dev question, but the reality is that my web apps have to
>interact with SQL Server so if anyone has any input, I'd love to hear
>it! Thanks.
Hi Roy,
This is impossible to answer, because the requirements are incomplete.
For instance, what happens if Jimmy edits rows x, y, and z (as in your
example), then Joan edits rows w and x, then Jimmy edits z another time
and then Jane views rows s through z. What columns in what rows have to
be marked as "changed"?
Another question - what if Jimmy edits rows x, y, and z; then nothin
happens for a long time. After a year, Jane looks at rows s through z.
Should Jimmy's changes still be marked?
Hugo Kornelis, SQL Server MVP|||>
> My question is: what is the most efficient way to track these column
> specific edits so that my web app can display them? This may seem like
> a web dev question, but the reality is that my web apps have to
> interact with SQL Server so if anyone has any input, I'd love to hear
> it! Thanks.
There are several ways to accomplish that. Do you want your system to
be optimized for retrieval of the current version but support
occasional drill down into editing history. Or do you want to optimize
retrieving history of edits and are ready to pay the price of slowing
down retrieval of current version?
Best design for a service that will monitor db
We have an existing database that is constantly receiving updated
information on the status and attributes of specific objects within the
application in batches. As these records come in, there is portions of the
table that they populate that are intentionally left empty, because the data
for these fields is retrieved from a seperate Java application through a
published web service (on same network). We are constructing a .Net service
which will handle the retrieval of records from the Java app and push the
new data into the relevant fields.
We are currently designing the .NET service to check the database on a
predefined interval, to see if any new records have appeared that need to be
looked up in the Java application. However, it would be preferable (at
least for testing) if this interaction could be designed so that the Sql
Server 2000 database could notify the .Net service that a new batch of
records has arrived (push instead of pull). Does anyone have any knowledge
if there is a means by which this can be accomplished?
Thanks.Hmmm ... There surely is a notificaiton service in SQL Server but as far as
I've read the documentation it maynot be suitable for this problem ...
But I think you can write a trigger that can call a DTS package or a Jobs
framework to do this notification ... I think this can also be one solution
...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"nfalconer" <navid@.gci.net> wrote in message
news:vm4o27o29cu596@.corp.supernews.com...
> We have an existing database that is constantly receiving updated
> information on the status and attributes of specific objects within the
> application in batches. As these records come in, there is portions of
the
> table that they populate that are intentionally left empty, because the
data
> for these fields is retrieved from a seperate Java application through a
> published web service (on same network). We are constructing a .Net
service
> which will handle the retrieval of records from the Java app and push the
> new data into the relevant fields.
> We are currently designing the .NET service to check the database on a
> predefined interval, to see if any new records have appeared that need to
be
> looked up in the Java application. However, it would be preferable (at
> least for testing) if this interaction could be designed so that the Sql
> Server 2000 database could notify the .Net service that a new batch of
> records has arrived (push instead of pull). Does anyone have any
knowledge
> if there is a means by which this can be accomplished?
> Thanks.
>
information on the status and attributes of specific objects within the
application in batches. As these records come in, there is portions of the
table that they populate that are intentionally left empty, because the data
for these fields is retrieved from a seperate Java application through a
published web service (on same network). We are constructing a .Net service
which will handle the retrieval of records from the Java app and push the
new data into the relevant fields.
We are currently designing the .NET service to check the database on a
predefined interval, to see if any new records have appeared that need to be
looked up in the Java application. However, it would be preferable (at
least for testing) if this interaction could be designed so that the Sql
Server 2000 database could notify the .Net service that a new batch of
records has arrived (push instead of pull). Does anyone have any knowledge
if there is a means by which this can be accomplished?
Thanks.Hmmm ... There surely is a notificaiton service in SQL Server but as far as
I've read the documentation it maynot be suitable for this problem ...
But I think you can write a trigger that can call a DTS package or a Jobs
framework to do this notification ... I think this can also be one solution
...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"nfalconer" <navid@.gci.net> wrote in message
news:vm4o27o29cu596@.corp.supernews.com...
> We have an existing database that is constantly receiving updated
> information on the status and attributes of specific objects within the
> application in batches. As these records come in, there is portions of
the
> table that they populate that are intentionally left empty, because the
data
> for these fields is retrieved from a seperate Java application through a
> published web service (on same network). We are constructing a .Net
service
> which will handle the retrieval of records from the Java app and push the
> new data into the relevant fields.
> We are currently designing the .NET service to check the database on a
> predefined interval, to see if any new records have appeared that need to
be
> looked up in the Java application. However, it would be preferable (at
> least for testing) if this interaction could be designed so that the Sql
> Server 2000 database could notify the .Net service that a new batch of
> records has arrived (push instead of pull). Does anyone have any
knowledge
> if there is a means by which this can be accomplished?
> Thanks.
>
Best Data Type for a Tracking ID?
Hello,
We're in the table design process and could really use outside thoughts on
our options.
The table in question will grow to several million records. This table will
update and insert depending on what the tracking ID does as it lives out it
day long life span.
Example Stored Proc:
UPDATE tbTracking
SET myCount = myCount +1
WHERE trackingID = @.trackingID AND productID = @.productID
IF (@.@.rowcount = 0)
BEGIN
INSERT INTO tbTracking (...) VALUES (...) ;
END
Everyday at midnight the table will be truncate just afer many other queries
generated reports and store cumulative data into other tables.
Our goal is extreme performance, with that in mind what is the best data
type for trackingID and productID? We have the luxury to make them whatever
is best, as this is a completely new project.
Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like,
but we're in need of advise from those with more experience with these
matters.
Thank you for your time.
MarkMark S. wrote:
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
This doesn't make much sense to me. As described it isn't clear whether
trackingid is a surrogate key or not. If it isn't then I don't
understand what its purpose is. What is the business meaning of
trackingid? Why isn't the datatype predetermined? What key or keys
exist in this table?
Maybe we are talking surrogate keys here, in which case see:
http://www.aspfaq.com/show.asp?id=2504
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
A guiding in principle in data warehouse applications is usually to
capture the data at the finest possible level of granularity. I expect
you've considered this, but I just thought it worth restating for the
benefit of all.
David Portas
SQL Server MVP
--|||Who or what determines the value of TrackingID?
If this key is to uniquely identify an event (such as a support call), then
perhaps (8 byte) datetime would be the logical choice. It has inherent
meaning, and unless you have hundreds of support calls coming in per minute,
it is unlikely that this key value would be duplicated, and retry logic on
the insert procedure or statement can handle the unlikely event that does.
There may even be a need to purge the tbTracking table based on < a specific
date/time rather than truncating the entire table at the end of the day.
As for ProductID, this should be an existing attribute in your database.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||In my experiences, int data type is the best performer especially when it
comes to indexing, joins, etc. (This comment relates to 32-bit O/S and SQL
Server 2000.)
I would make a meaningless key:
TrackingRowId int, identity, primary key, clustered
ProductRowId int, identity, primary key, clustered
SQL Server takes care of assigning the next number. The clustered index
will always insert data at the end of the data pages which avoids page split
s.
If you want to reset the tracking id each midnight, simply change the reseed
the tables as part of your processing.
Personally...
I would NOT use a character string for a key because its slower to compare
strings.
I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
butt when you have to write a manual query to pull data.
Finally, I have to reiterate David's point of capturing data at the lowest
level. Are you sure you want to summarize and truncate on a nightly basis.
What if a bug is discovered? You won't have the original data to recreate
the totals. What if a new way of summarizing is added? You won't have the
original data to drill into or summarize.
And, to reiterate JT's point of having a datetime column that can be very
handy. It is debateable whether to use it as the key or not. If you're
truncating the tables each night, it may be a waste.
Just my two cents,
Joe
"Mark S." wrote:
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
>
>|||Gentleman thank you for your feedback.
Joe, other than the UniqueIndentifier being unfriendly for humans, do you
find it faster than INTs?
As to th other questions and caveats, all that has been considered
previously, and wasn't mentioned in my question in the interests of brevity
and an attempt to focus my question on a single point. Regardless, thank you
for your full consideration.
Cheers,
Mark
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:50A0324B-1446-4044-A850-706B59A04169@.microsoft.com...
> In my experiences, int data type is the best performer especially when it
> comes to indexing, joins, etc. (This comment relates to 32-bit O/S and
> SQL
> Server 2000.)
> I would make a meaningless key:
> TrackingRowId int, identity, primary key, clustered
> ProductRowId int, identity, primary key, clustered
> SQL Server takes care of assigning the next number. The clustered index
> will always insert data at the end of the data pages which avoids page
> splits.
> If you want to reset the tracking id each midnight, simply change the
> reseed
> the tables as part of your processing.
> Personally...
> I would NOT use a character string for a key because its slower to compare
> strings.
> I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
> butt when you have to write a manual query to pull data.
> Finally, I have to reiterate David's point of capturing data at the lowest
> level. Are you sure you want to summarize and truncate on a nightly
> basis.
> What if a bug is discovered? You won't have the original data to recreate
> the totals. What if a new way of summarizing is added? You won't have
> the
> original data to drill into or summarize.
> And, to reiterate JT's point of having a datetime column that can be very
> handy. It is debateable whether to use it as the key or not. If you're
> truncating the tables each night, it may be a waste.
> Just my two cents,
> Joe
> "Mark S." wrote:
>|||The last line of the article David suggested
http://www.aspfaq.com/show.asp?id=2504
Says alot about GUI() not being optimized, if anyone disagrees, feel free to
speak up:
"the wider datatype leads to a drop in index performance (if clustered, each
insert almost guaranteed to 'dirty' a different page), and an increase in
storage requirements; " and five other cons.
Thank you.|||To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
WITH FILLFACTOR = 100
This causes every new row to be added at the end of the table--minimizing
index maintenance and eliminating page splits. Use caution, however: since
SQL Server doesn't automatically reorganize indexes, a high volume of DELETE
activity will cause the index to become sparse, which can reduce SELECT
performance, but it will not affect INSERT performance.
The INT datatype matches the word size of most Intel processors (32-bit), so
comparisons require fewer CPU cycles.
The stored procedure is an example of what not to do. It's a recipie for
primary key constraint violations. There's nothing that prevents two
transactions from trying to INSERT the same information at the same time.
Most of the time what will happen is that one connection will succeed with
the INSERT and the other will UPDATE the newly inserted row, but a collision
will occur if identical UPDATE statements occur simultaneously on two
unbound connections followed by (since the row doesn't yet exist)
simultaneous identical INSERT statements.
The correct method is to use something like this:
BEGIN TRAN
IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
UPDATE
ELSE
INSERT
COMMIT
Some people use the following instead, but I prefer the above method since
it is easier to read and understand
BEGIN TRAN
INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
IF @.@.ROWCOUNT = 0
UPDATE
COMMIT
Note that there isn't any marked reduction in performance or concurrency
between this and your sample, because UPDLOCK doesn't block SELECTs, and any
blocking that does occur is necessary to maintain integrity. Without the
EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
anyway which involves reading the index page into memory. With the EXISTS
clause, the SELECT reads the index page and applies an update lock, and the
INSERT or UPDATE simply transition from an update lock to an exclusive lock
in memory--no additional physical read is necessary.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||Thank you very much.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23oG2PTDDGHA.3980@.TK2MSFTNGP12.phx.gbl...
> To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY
> CLUSTERED WITH FILLFACTOR = 100
> This causes every new row to be added at the end of the table--minimizing
> index maintenance and eliminating page splits. Use caution, however:
> since SQL Server doesn't automatically reorganize indexes, a high volume
> of DELETE activity will cause the index to become sparse, which can reduce
> SELECT performance, but it will not affect INSERT performance.
> The INT datatype matches the word size of most Intel processors (32-bit),
> so comparisons require fewer CPU cycles.
> The stored procedure is an example of what not to do. It's a recipie for
> primary key constraint violations. There's nothing that prevents two
> transactions from trying to INSERT the same information at the same time.
> Most of the time what will happen is that one connection will succeed with
> the INSERT and the other will UPDATE the newly inserted row, but a
> collision will occur if identical UPDATE statements occur simultaneously
> on two unbound connections followed by (since the row doesn't yet exist)
> simultaneous identical INSERT statements.
> The correct method is to use something like this:
> BEGIN TRAN
> IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
> UPDATE
> ELSE
> INSERT
> COMMIT
> Some people use the following instead, but I prefer the above method since
> it is easier to read and understand
> BEGIN TRAN
> INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
> IF @.@.ROWCOUNT = 0
> UPDATE
> COMMIT
> Note that there isn't any marked reduction in performance or concurrency
> between this and your sample, because UPDLOCK doesn't block SELECTs, and
> any blocking that does occur is necessary to maintain integrity. Without
> the EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
> anyway which involves reading the index page into memory. With the EXISTS
> clause, the SELECT reads the index page and applies an update lock, and
> the INSERT or UPDATE simply transition from an update lock to an exclusive
> lock in memory--no additional physical read is necessary.
> "Mark S." <marks@.yahoo.com> wrote in message
> news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
>
We're in the table design process and could really use outside thoughts on
our options.
The table in question will grow to several million records. This table will
update and insert depending on what the tracking ID does as it lives out it
day long life span.
Example Stored Proc:
UPDATE tbTracking
SET myCount = myCount +1
WHERE trackingID = @.trackingID AND productID = @.productID
IF (@.@.rowcount = 0)
BEGIN
INSERT INTO tbTracking (...) VALUES (...) ;
END
Everyday at midnight the table will be truncate just afer many other queries
generated reports and store cumulative data into other tables.
Our goal is extreme performance, with that in mind what is the best data
type for trackingID and productID? We have the luxury to make them whatever
is best, as this is a completely new project.
Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like,
but we're in need of advise from those with more experience with these
matters.
Thank you for your time.
MarkMark S. wrote:
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
This doesn't make much sense to me. As described it isn't clear whether
trackingid is a surrogate key or not. If it isn't then I don't
understand what its purpose is. What is the business meaning of
trackingid? Why isn't the datatype predetermined? What key or keys
exist in this table?
Maybe we are talking surrogate keys here, in which case see:
http://www.aspfaq.com/show.asp?id=2504
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
A guiding in principle in data warehouse applications is usually to
capture the data at the finest possible level of granularity. I expect
you've considered this, but I just thought it worth restating for the
benefit of all.
David Portas
SQL Server MVP
--|||Who or what determines the value of TrackingID?
If this key is to uniquely identify an event (such as a support call), then
perhaps (8 byte) datetime would be the logical choice. It has inherent
meaning, and unless you have hundreds of support calls coming in per minute,
it is unlikely that this key value would be duplicated, and retry logic on
the insert procedure or statement can handle the unlikely event that does.
There may even be a need to purge the tbTracking table based on < a specific
date/time rather than truncating the entire table at the end of the day.
As for ProductID, this should be an existing attribute in your database.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||In my experiences, int data type is the best performer especially when it
comes to indexing, joins, etc. (This comment relates to 32-bit O/S and SQL
Server 2000.)
I would make a meaningless key:
TrackingRowId int, identity, primary key, clustered
ProductRowId int, identity, primary key, clustered
SQL Server takes care of assigning the next number. The clustered index
will always insert data at the end of the data pages which avoids page split
s.
If you want to reset the tracking id each midnight, simply change the reseed
the tables as part of your processing.
Personally...
I would NOT use a character string for a key because its slower to compare
strings.
I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
butt when you have to write a manual query to pull data.
Finally, I have to reiterate David's point of capturing data at the lowest
level. Are you sure you want to summarize and truncate on a nightly basis.
What if a bug is discovered? You won't have the original data to recreate
the totals. What if a new way of summarizing is added? You won't have the
original data to drill into or summarize.
And, to reiterate JT's point of having a datetime column that can be very
handy. It is debateable whether to use it as the key or not. If you're
truncating the tables each night, it may be a waste.
Just my two cents,
Joe
"Mark S." wrote:
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table wil
l
> update and insert depending on what the tracking ID does as it lives out i
t
> day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other queri
es
> generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them whateve
r
> is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the like
,
> but we're in need of advise from those with more experience with these
> matters.
> Thank you for your time.
> Mark
>
>|||Gentleman thank you for your feedback.
Joe, other than the UniqueIndentifier being unfriendly for humans, do you
find it faster than INTs?
As to th other questions and caveats, all that has been considered
previously, and wasn't mentioned in my question in the interests of brevity
and an attempt to focus my question on a single point. Regardless, thank you
for your full consideration.
Cheers,
Mark
"Joe from WI" <JoefromWI@.discussions.microsoft.com> wrote in message
news:50A0324B-1446-4044-A850-706B59A04169@.microsoft.com...
> In my experiences, int data type is the best performer especially when it
> comes to indexing, joins, etc. (This comment relates to 32-bit O/S and
> SQL
> Server 2000.)
> I would make a meaningless key:
> TrackingRowId int, identity, primary key, clustered
> ProductRowId int, identity, primary key, clustered
> SQL Server takes care of assigning the next number. The clustered index
> will always insert data at the end of the data pages which avoids page
> splits.
> If you want to reset the tracking id each midnight, simply change the
> reseed
> the tables as part of your processing.
> Personally...
> I would NOT use a character string for a key because its slower to compare
> strings.
> I would NOT use a UNIQUEIDENTIFIER for a key because it is a pain in the
> butt when you have to write a manual query to pull data.
> Finally, I have to reiterate David's point of capturing data at the lowest
> level. Are you sure you want to summarize and truncate on a nightly
> basis.
> What if a bug is discovered? You won't have the original data to recreate
> the totals. What if a new way of summarizing is added? You won't have
> the
> original data to drill into or summarize.
> And, to reiterate JT's point of having a datetime column that can be very
> handy. It is debateable whether to use it as the key or not. If you're
> truncating the tables each night, it may be a waste.
> Just my two cents,
> Joe
> "Mark S." wrote:
>|||The last line of the article David suggested
http://www.aspfaq.com/show.asp?id=2504
Says alot about GUI() not being optimized, if anyone disagrees, feel free to
speak up:
"the wider datatype leads to a drop in index performance (if clustered, each
insert almost guaranteed to 'dirty' a different page), and an increase in
storage requirements; " and five other cons.
Thank you.|||To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED
WITH FILLFACTOR = 100
This causes every new row to be added at the end of the table--minimizing
index maintenance and eliminating page splits. Use caution, however: since
SQL Server doesn't automatically reorganize indexes, a high volume of DELETE
activity will cause the index to become sparse, which can reduce SELECT
performance, but it will not affect INSERT performance.
The INT datatype matches the word size of most Intel processors (32-bit), so
comparisons require fewer CPU cycles.
The stored procedure is an example of what not to do. It's a recipie for
primary key constraint violations. There's nothing that prevents two
transactions from trying to INSERT the same information at the same time.
Most of the time what will happen is that one connection will succeed with
the INSERT and the other will UPDATE the newly inserted row, but a collision
will occur if identical UPDATE statements occur simultaneously on two
unbound connections followed by (since the row doesn't yet exist)
simultaneous identical INSERT statements.
The correct method is to use something like this:
BEGIN TRAN
IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
UPDATE
ELSE
INSERT
COMMIT
Some people use the following instead, but I prefer the above method since
it is easier to read and understand
BEGIN TRAN
INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
IF @.@.ROWCOUNT = 0
UPDATE
COMMIT
Note that there isn't any marked reduction in performance or concurrency
between this and your sample, because UPDLOCK doesn't block SELECTs, and any
blocking that does occur is necessary to maintain integrity. Without the
EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
anyway which involves reading the index page into memory. With the EXISTS
clause, the SELECT reads the index page and applies an update lock, and the
INSERT or UPDATE simply transition from an update lock to an exclusive lock
in memory--no additional physical read is necessary.
"Mark S." <marks@.yahoo.com> wrote in message
news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> We're in the table design process and could really use outside thoughts on
> our options.
> The table in question will grow to several million records. This table
> will update and insert depending on what the tracking ID does as it lives
> out it day long life span.
> Example Stored Proc:
> UPDATE tbTracking
> SET myCount = myCount +1
> WHERE trackingID = @.trackingID AND productID = @.productID
> IF (@.@.rowcount = 0)
> BEGIN
> INSERT INTO tbTracking (...) VALUES (...) ;
> END
> Everyday at midnight the table will be truncate just afer many other
> queries generated reports and store cumulative data into other tables.
> Our goal is extreme performance, with that in mind what is the best data
> type for trackingID and productID? We have the luxury to make them
> whatever is best, as this is a completely new project.
> Internally debated NVARCHR(50), INT, Binary, UNIQUEIDENTIFIER and the
> like, but we're in need of advise from those with more experience with
> these matters.
> Thank you for your time.
> Mark
>|||Thank you very much.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:%23oG2PTDDGHA.3980@.TK2MSFTNGP12.phx.gbl...
> To maximize INSERT performance: use INT IDENTITY(1, 1) PRIMARY KEY
> CLUSTERED WITH FILLFACTOR = 100
> This causes every new row to be added at the end of the table--minimizing
> index maintenance and eliminating page splits. Use caution, however:
> since SQL Server doesn't automatically reorganize indexes, a high volume
> of DELETE activity will cause the index to become sparse, which can reduce
> SELECT performance, but it will not affect INSERT performance.
> The INT datatype matches the word size of most Intel processors (32-bit),
> so comparisons require fewer CPU cycles.
> The stored procedure is an example of what not to do. It's a recipie for
> primary key constraint violations. There's nothing that prevents two
> transactions from trying to INSERT the same information at the same time.
> Most of the time what will happen is that one connection will succeed with
> the INSERT and the other will UPDATE the newly inserted row, but a
> collision will occur if identical UPDATE statements occur simultaneously
> on two unbound connections followed by (since the row doesn't yet exist)
> simultaneous identical INSERT statements.
> The correct method is to use something like this:
> BEGIN TRAN
> IF EXISTS (SELECT WITH(UPDLOCK, HOLDLOCK))
> UPDATE
> ELSE
> INSERT
> COMMIT
> Some people use the following instead, but I prefer the above method since
> it is easier to read and understand
> BEGIN TRAN
> INSERT...SELECT...WHERE NOT EXISTS(SELECT WITH(UPDLOCK, HOLDLOCK))
> IF @.@.ROWCOUNT = 0
> UPDATE
> COMMIT
> Note that there isn't any marked reduction in performance or concurrency
> between this and your sample, because UPDLOCK doesn't block SELECTs, and
> any blocking that does occur is necessary to maintain integrity. Without
> the EXISTS clause, the INSERT or UPDATE will be applying an exclusive lock
> anyway which involves reading the index page into memory. With the EXISTS
> clause, the SELECT reads the index page and applies an update lock, and
> the INSERT or UPDATE simply transition from an update lock to an exclusive
> lock in memory--no additional physical read is necessary.
> "Mark S." <marks@.yahoo.com> wrote in message
> news:Ow6CKQ9CGHA.4004@.tk2msftngp13.phx.gbl...
>
Best data base design
Hi all,
Can any one of you help me out in giving the ideal database design for the following requirement.
I will be having two heirachy of data. One which can be accesed by the master user and others by clients. There can be n number of clients who can be there. A client can view only his details and the masters.
A master can view data of all the clients and the master. Each clients detail is expected to be kept in a different table.
So in an apllication if the person who logs in is a master user we need to show details of all clients.
If the person logs in as a client A then details of only his table is to be shown.
Can any one get back on this,
Thanking you in advance
VyasFor what its worth ...
I would only use Views to allow them to see the data, and stored procedures to update.
For viewing data, I would create an association table with logins and allowed entities
create table SecurityControl
(
securitycontrol_id int identity not null ,
user varchar(40) not null,
company_no int not null
)
then in the views i would join to it like so:
select
ci.*
from CompanyInfo ci
join SecurityControl si on si.company_no = ci.company_no and suser_name() = si.user
SQL 2000 does have row level security capability but
1. I haven't used it enough to explain it.
2. You didn't specific a version and this solution would be portable to most RDBMS's
Can any one of you help me out in giving the ideal database design for the following requirement.
I will be having two heirachy of data. One which can be accesed by the master user and others by clients. There can be n number of clients who can be there. A client can view only his details and the masters.
A master can view data of all the clients and the master. Each clients detail is expected to be kept in a different table.
So in an apllication if the person who logs in is a master user we need to show details of all clients.
If the person logs in as a client A then details of only his table is to be shown.
Can any one get back on this,
Thanking you in advance
VyasFor what its worth ...
I would only use Views to allow them to see the data, and stored procedures to update.
For viewing data, I would create an association table with logins and allowed entities
create table SecurityControl
(
securitycontrol_id int identity not null ,
user varchar(40) not null,
company_no int not null
)
then in the views i would join to it like so:
select
ci.*
from CompanyInfo ci
join SecurityControl si on si.company_no = ci.company_no and suser_name() = si.user
SQL 2000 does have row level security capability but
1. I haven't used it enough to explain it.
2. You didn't specific a version and this solution would be portable to most RDBMS's
Friday, February 24, 2012
Best book for relational db's?
Greetings,
I'm reentering SQLServer-land after an absense. I've forgotten most of
my school-taught relational/OO database design theory. Normalization is
still in the old memory banks, but it too is fading fast. :)
Can anyone recommend a good book on relational design that can refresh
my skills?
Thanks!See if this helps:
http://www.datamodel.org/DataModelLibrary.html
AMB
"Guerrilla_Coder" wrote:
> Greetings,
> I'm reentering SQLServer-land after an absense. I've forgotten most of
> my school-taught relational/OO database design theory. Normalization is
> still in the old memory banks, but it too is fading fast. :)
> Can anyone recommend a good book on relational design that can refresh
> my skills?
> Thanks!
>
I'm reentering SQLServer-land after an absense. I've forgotten most of
my school-taught relational/OO database design theory. Normalization is
still in the old memory banks, but it too is fading fast. :)
Can anyone recommend a good book on relational design that can refresh
my skills?
Thanks!See if this helps:
http://www.datamodel.org/DataModelLibrary.html
AMB
"Guerrilla_Coder" wrote:
> Greetings,
> I'm reentering SQLServer-land after an absense. I've forgotten most of
> my school-taught relational/OO database design theory. Normalization is
> still in the old memory banks, but it too is fading fast. :)
> Can anyone recommend a good book on relational design that can refresh
> my skills?
> Thanks!
>
Best and worst scenarios for Notification Services
Hi,
I am just looking for the best and worst scenarios to implement Notification services...This is more to come up with a design guildelines
I Would be more interested in the worst sceanrios :) Please share your inputs based on your experiences...
Regards,
Anjana
I'm not really sure that I understand what you mean by worst case scenario. Do you mean situations where SQLNS is not the best tool for the job? Or do you mean specific configurations that would cause SQLNS to perform poorly.
In either case, I guess, that's going to largely depend on the situation.
|||I meant where SQLNS should not be used...
For example let's take three cases
News Alerts (changes with reasonable freqeuncy, and alerts must reach the subscriber in reasonable time)
Weather changes alerts(changes very frequently,and alerts can reach the subscriber in reasonable time)
Stock market alerts (changes very frequently and alert needs to be sent immedaitely to the subscriber with no delay).
In the above scenarios are there any scenarios where NS might perform a bit poorly?
Regards,
Anjana
|||SQLNS is very good at matching lots of event data with lots of subscriber/subscription data and generating lots of events. It is designed to be "internet scalable" where you may not know just how many subscribers, subscriptions, or even events that you maybe processing over a given time period. It's equally as good for smaller applications, too.
For smaller, purely in-house notification applications, sometimes Query Notifications are a better choice. It just depends on what you're trying to accomplish.
For cases where sub-second notifications are required, SLQNS is *not* the best tool. The generator fires on a regular basis to evaluate whether there has been any activities that it needs to process. Same for the distributor. If you must be notified immediately, that is without a possible 2 second delay, SLQNS is not for you.
HTH...
Friday, February 10, 2012
Begginer in SQL-Foreign KEy to Mulitple Tables
Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
Hello,
They question I have is that in the 1'st SQL statement that you gave it says "selecy * from property p inner joun mountain_property mp" which shows me that this statement already knows that it is seaching in the mountain_property table.
But what would you do if you didn't know which table to search in. As in, lets say you have your property instance (row) in the property table, and you now want to get more information about it(which is either stored in the mountain_property or beach_property table). I think I can see myself doing this in two statements by first getting the property_type and then processing that data in my application to choose what table to choose in and then executing another statement to get the data. Is this the way that you are supposed to do it. Eg below
So I guess if I was in the Property table and I had selected row which was of type mountain_property. To get the additional information of this property (held in the moutain_property table) I would first select the property_type id. I would then use an if/else statement in my application to find out wich table to search for. eg. If(type == 1) table = Mountain; else if(type == 2) table = Beach;. Then I would make another SQL statement that would then search for the data in the table name that I got from the if/else statement.
Is this right, or am I far off from course?
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
Hello,
Let's say that we have the following tables:
property (property_id int primary key, property_type int, some general fields)
mountain_property (mproperty_id int primary key, property_id int references property(property_id), some specific fields)
beach_property (bproperty_id int primary key, property_id int references property(property_id), some specific fields)
property_type defines the mountain (1) or beach (2) property.
So when we need to select all records for mountain property we can execute the following SQL:
selecy * from property p inner joun mountain_property mp on p.property_id = mp.property_id where property_type=1.
So genenerally, property table knows nothing about mountain_property and beach_property and here is no relationships from property to any of these tables.
This example is similar to defining a class with collections and this type of relationships is called one-to-many (for each of pair property-mountain_property and property-beach_property).
The other possible (but different) solution is to use one-to-one relationship. In that case mountain_property's primary key is a foreign key to property table. E.g. the structure is:
property (property_id int primary key, property_type int, some general fields)
mountain_property (property_id int primary key references property(property_id), some specific fields)
and
selecy * from property p inner joun mountain_property mp on p.property_id = mp.property_id
will return a list of "mountain_property" objects with all fields.
Sincerely,
Alex|||
There are a couple of examples of what you're after in the Microsoft sample AdventureWorks database.
For example the Store and Individual tables both inherit from the Customer table. Store and Individual both have a primary key of CustomerId, which is a foreign key to the Customer table. Customer has a CustomerType column, which contains either 'S' or 'I' depending on whether the Customer row refers to a Store or an Individual.
This is also the way that my Foundation product implements inheritance; it looks for relationships like this and creates business / data access objects with the same inheritance structure. You can download either the free/trial version (free for up to 20 tables) or just the Knowledge Base from my site, the Knowledge base has a helpful page in it about exactly this stuff, it's called "Defining Inheritance Relationships".
Linq-to-Sql implements inheritance in a different way. You don't have a base table and derived tables. Everything goes into a single table, and any columns that would normally go into a derived table must be made nullable. So you end up with loads of nullable columns, only a subset of which can legitimately be populated for any particular row. Of course having to make the columns nullable means a lack of data-integrity, and you can't have unique indexes that use those columns, and no one-to-one relationships including those columns. Made me shudder when I read about it! Maybe I'm just too sensitive
Linq-to-Sql still uses the "CustomerType column" way of telling what derived type a row is.
Sean
|||Thank your for giving such a detailed example, it really helped. I guess I understand here that there wont be a direct link for the Property table to find one of its children, but that it can find them because it holds the property_type id so it will know what table to search for them in.They question I have is that in the 1'st SQL statement that you gave it says "selecy * from property p inner joun mountain_property mp" which shows me that this statement already knows that it is seaching in the mountain_property table.
But what would you do if you didn't know which table to search in. As in, lets say you have your property instance (row) in the property table, and you now want to get more information about it(which is either stored in the mountain_property or beach_property table). I think I can see myself doing this in two statements by first getting the property_type and then processing that data in my application to choose what table to choose in and then executing another statement to get the data. Is this the way that you are supposed to do it. Eg below
So I guess if I was in the Property table and I had selected row which was of type mountain_property. To get the additional information of this property (held in the moutain_property table) I would first select the property_type id. I would then use an if/else statement in my application to find out wich table to search for. eg. If(type == 1) table = Mountain; else if(type == 2) table = Beach;. Then I would make another SQL statement that would then search for the data in the table name that I got from the if/else statement.
Is this right, or am I far off from course?
Been a DBA for too long.. want to get into DB Design and modelling
I have been a DBA for a while now and while doing that, i've never got my
feet wet in say development nor into database design and modelling.
Can someone tell me if there are any courses,etc. out there that i can take
where i could blend my DBA skills which is just the basics..
backups,security, performance tuning.. into a more well rounded DB
individual.
Thanks...Hassan wrote:
> I have been a DBA for a while now and while doing that, i've never got my
> feet wet in say development nor into database design and modelling.
> Can someone tell me if there are any courses,etc. out there that i can take
> where i could blend my DBA skills which is just the basics..
> backups,security, performance tuning.. into a more well rounded DB
> individual.
> Thanks...
>
Since you already have a decent background with the platform, I'd
suggest reading "The Art Of SQL".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
feet wet in say development nor into database design and modelling.
Can someone tell me if there are any courses,etc. out there that i can take
where i could blend my DBA skills which is just the basics..
backups,security, performance tuning.. into a more well rounded DB
individual.
Thanks...Hassan wrote:
> I have been a DBA for a while now and while doing that, i've never got my
> feet wet in say development nor into database design and modelling.
> Can someone tell me if there are any courses,etc. out there that i can take
> where i could blend my DBA skills which is just the basics..
> backups,security, performance tuning.. into a more well rounded DB
> individual.
> Thanks...
>
Since you already have a decent background with the platform, I'd
suggest reading "The Art Of SQL".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Been a DBA for too long.. want to get into DB Design and modelling
I have been a DBA for a while now and while doing that, i've never got my
feet wet in say development nor into database design and modelling.
Can someone tell me if there are any courses,etc. out there that i can take
where i could blend my DBA skills which is just the basics..
backups,security, performance tuning.. into a more well rounded DB
individual.
Thanks...Hassan wrote:
> I have been a DBA for a while now and while doing that, i've never got my
> feet wet in say development nor into database design and modelling.
> Can someone tell me if there are any courses,etc. out there that i can tak
e
> where i could blend my DBA skills which is just the basics..
> backups,security, performance tuning.. into a more well rounded DB
> individual.
> Thanks...
>
Since you already have a decent background with the platform, I'd
suggest reading "The Art Of SQL".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
feet wet in say development nor into database design and modelling.
Can someone tell me if there are any courses,etc. out there that i can take
where i could blend my DBA skills which is just the basics..
backups,security, performance tuning.. into a more well rounded DB
individual.
Thanks...Hassan wrote:
> I have been a DBA for a while now and while doing that, i've never got my
> feet wet in say development nor into database design and modelling.
> Can someone tell me if there are any courses,etc. out there that i can tak
e
> where i could blend my DBA skills which is just the basics..
> backups,security, performance tuning.. into a more well rounded DB
> individual.
> Thanks...
>
Since you already have a decent background with the platform, I'd
suggest reading "The Art Of SQL".
Tracy McKibben
MCDBA
http://www.realsqlguy.com
Subscribe to:
Posts (Atom)