Database Id prb !

Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped
to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/pr...oads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodin...ions/books.mspx
========================================
==========
"Prasad" <ekke_nikhil@.yahoo.co.uk> bl
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by
connecting through the
dedicated administrator connection (DAC), looking at the source code from th
e view sys.databases and
then do a select directly against the physical system table that the view sy
s.databases uses.
I don't know what you want to achieve in the end, but best bet is probably t
o disregard objects that
lives in the database with id 32767.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.g
bl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005
, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of
the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea
to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databas
es ?
> I also wanted information as to how do I query the system tables, it giv
es me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mappe
d to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with th
e
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/pr...oads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodin...ions/books.mspx
> ========================================
==========
> "Prasad" <ekke_nikhil@.yahoo.co.uk> bl
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl g...
> 2005,
> that
> ?
>|||Some tables are flagged as user tables even though they are shipped with the
product. The backup
history tables are such examples.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.g
bl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing a
s told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a
System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>sql

Database Id prb !

Hi,
I was profiling the Audit Schema Object Access Event in SQL Server 2005,
and when I executed the system stored procedure "sys.xp_msver" I found that
the database id of the this procedure is shown as "32767" which doesn't
exists in the sys.databases view ? Any idea to what database it belongs to ?
Does the above belong to the "mssqlsystemresource" or "distmdl"
databases ?
I also wanted information as to how do I query the system tables, it
gives me an error saying the object doesnt exists
Experts pls help
TIA
Thanks
P[1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
any database.
- Breaking changes to database engine features in SQL Server 2005
http://msdn2.microsoft.com/en-us/library/ms143179.aspx
[2] To query a system table, you will need to prefix the table with the
appropriate database that you are going to query. E.g.,
SELECT * FROM msdb..backupfile
- Querying the SQL Server System Catalog
http://msdn2.microsoft.com/en-us/library/ms189082.aspx
- Querying the SQL Server System Catalog FAQ
http://msdn2.microsoft.com/en-us/library/ms345522.aspx
--
Martin Poon
Microsoft MVP - SQL Server
----
- SQL Server 2005 books online
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
- SQL Server 2000 books online
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
=================================================="Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server
2005,
> and when I executed the system stored procedure "sys.xp_msver" I found
that
> the database id of the this procedure is shown as "32767" which doesn't
> exists in the sys.databases view ? Any idea to what database it belongs to
?
> Does the above belong to the "mssqlsystemresource" or "distmdl"
> databases ?
> I also wanted information as to how do I query the system tables, it
> gives me an error saying the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Yes, sys.xp_msver lives in the system resource database. You can see this by connecting through the
dedicated administrator connection (DAC), looking at the source code from the view sys.databases and
then do a select directly against the physical system table that the view sys.databases uses.
I don't know what you want to achieve in the end, but best bet is probably to disregard objects that
lives in the database with id 32767.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I was profiling the Audit Schema Object Access Event in SQL Server 2005, and when I executed
> the system stored procedure "sys.xp_msver" I found that the database id of the this procedure is
> shown as "32767" which doesn't exists in the sys.databases view ? Any idea to what database it
> belongs to ?
> Does the above belong to the "mssqlsystemresource" or "distmdl" databases ?
> I also wanted information as to how do I query the system tables, it gives me an error saying
> the object doesnt exists
> Experts pls help
> TIA
> Thanks
> P
>|||Hi,
Thnx for your information, but I have a question, this must be a naive
for you.
But if I query from a database "db" context shouldnt the profiler show
that the database name is "db" and database id is the id the database "db" ?
Same thing for the tables also ?
The sys.all_objects lists that these are system tables and system stored
procedures but then why it is not showing that it comes from this database
in the profiler?
I want to say to the profiler that I want to monitor all the "Audit
Schema Object Access Event" in this particular database
But then since these SP's and tables have the database id different then
what is configured it is ignored even when these SP's are fired in the
database context of what is configured
Any suggestions ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Hi,
Regarding your suggestion for the system table I tried the same thing as
told by you "select * from msdb..backupfile"
But in the profiler it still returns that as a User table instead of a
System table.
Any ideas ?
Thanks
P
"Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
> any database.
> - Breaking changes to database engine features in SQL Server 2005
> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
> [2] To query a system table, you will need to prefix the table with the
> appropriate database that you are going to query. E.g.,
> SELECT * FROM msdb..backupfile
> - Querying the SQL Server System Catalog
> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
> - Querying the SQL Server System Catalog FAQ
> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
> --
> Martin Poon
> Microsoft MVP - SQL Server
> ----
> - SQL Server 2005 books online
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> - SQL Server 2000 books online
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
> ==================================================> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs
>> to
> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>|||Some tables are flagged as user tables even though they are shipped with the product. The backup
history tables are such examples.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message news:uIVVeYi1GHA.4300@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Regarding your suggestion for the system table I tried the same thing as told by you "select *
> from msdb..backupfile"
> But in the profiler it still returns that as a User table instead of a System table.
> Any ideas ?
> Thanks
> P
> "Martin Poon [MVP]" <martinpoon@.graduate(.)hku(.)hk> wrote in message
> news:u40Vtxa1GHA.4448@.TK2MSFTNGP04.phx.gbl...
>> [1] Database ID 32767 is reserved in SQL Server 2005, and is not mapped to
>> any database.
>> - Breaking changes to database engine features in SQL Server 2005
>> http://msdn2.microsoft.com/en-us/library/ms143179.aspx
>> [2] To query a system table, you will need to prefix the table with the
>> appropriate database that you are going to query. E.g.,
>> SELECT * FROM msdb..backupfile
>> - Querying the SQL Server System Catalog
>> http://msdn2.microsoft.com/en-us/library/ms189082.aspx
>> - Querying the SQL Server System Catalog FAQ
>> http://msdn2.microsoft.com/en-us/library/ms345522.aspx
>> --
>> Martin Poon
>> Microsoft MVP - SQL Server
>> ----
>> - SQL Server 2005 books online
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>> - SQL Server 2000 books online
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>> ==================================================>> "Prasad" <ekke_nikhil@.yahoo.co.uk> ¦b¶l¥ó
>> news:e1BoWNa1GHA.3656@.TK2MSFTNGP04.phx.gbl ¤¤¼¶¼g...
>> Hi,
>> I was profiling the Audit Schema Object Access Event in SQL Server
>> 2005,
>> and when I executed the system stored procedure "sys.xp_msver" I found
>> that
>> the database id of the this procedure is shown as "32767" which doesn't
>> exists in the sys.databases view ? Any idea to what database it belongs to
>> ?
>> Does the above belong to the "mssqlsystemresource" or "distmdl"
>> databases ?
>> I also wanted information as to how do I query the system tables, it
>> gives me an error saying the object doesnt exists
>> Experts pls help
>> TIA
>> Thanks
>> P
>>
>>
>

Database diagrams

Can a Database Diagram be imported or exported so that the schema can be
shown to someone that does not have Enterprise Manager installed on their
system?
If so what tools can do this?
No; you can do a screen capture or print to a PDF, perhaps.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Jim Abel" <JimAbel@.discussions.microsoft.com> wrote in message
news:65C71846-71F2-4875-B0C8-79C18104F5CF@.microsoft.com...
> Can a Database Diagram be imported or exported so that the schema can be
> shown to someone that does not have Enterprise Manager installed on their
> system?
> If so what tools can do this?

Database diagrams

Can a Database Diagram be imported or exported so that the schema can be
shown to someone that does not have Enterprise Manager installed on their
system?
If so what tools can do this?No; you can do a screen capture or print to a PDF, perhaps.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jim Abel" <JimAbel@.discussions.microsoft.com> wrote in message
news:65C71846-71F2-4875-B0C8-79C18104F5CF@.microsoft.com...
> Can a Database Diagram be imported or exported so that the schema can be
> shown to someone that does not have Enterprise Manager installed on their
> system?
> If so what tools can do this?

Database diagrams

Can a Database Diagram be imported or exported so that the schema can be
shown to someone that does not have Enterprise Manager installed on their
system?
If so what tools can do this?No; you can do a screen capture or print to a PDF, perhaps.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jim Abel" <JimAbel@.discussions.microsoft.com> wrote in message
news:65C71846-71F2-4875-B0C8-79C18104F5CF@.microsoft.com...
> Can a Database Diagram be imported or exported so that the schema can be
> shown to someone that does not have Enterprise Manager installed on their
> system?
> If so what tools can do this?

Database Designing tools

Hi all,
Does anyone know how I can design the database schema. I mean what tools can be used to the design the database and view the table relationships, etc. TIA.

Vik!Enterprise Manager? Makes a pretty good job in SQL Server for this.

Database design question - Using Field definition table

Hi,
We have a table similar to the following schema.
Product table
product_id int,
product_name varchar(50),
product_desc varchar(100),
product_price money,
product_sub_category_id int
We also have tables for product category and sub category. We would
like to save audit trail information whenever a new product is added,
changed name, description or price, and moved to another sub category
or category. We need to save modified user, modification datetime and
some other audit info as well. It seems there are two options available
saving this data i.e. either using a large table that has all audit
columns or using field definition table. The field definition table has
a field id and audit field column and the audit table will have
product_id, modified field id, new value, old value, modified user and
modification date.
My question is which design is efficient? It seems the first is pretty
straight forward and easy to implement, but stores redundant
information and grows quickly. The second solution seems to reduce
redundancy, but end up saving multiple rows if there are multiple
updates at one time. Also, we need to make join to the same table when
we write complex queries.
Any comments or suggestions would be appreciated.
Thanks,It sounds like you already understand the tradeoffs pretty well, at
least between the alternatives given.
Not knowing the details of your operation makes it hard to give
meaningful advice. Just looking at the given information, I have to
suspect that the most common change would be to product_price. It
also makes me wonder if the price does not belong in a price table
instead, with effective dates and who-changed-it-when information. In
that configuration the price table itself becomes its own audit table.
If changes to the rest of the columns are infrequent enough then that
would allow the simple approach of duplicating the entire row for the
other columns. When space permits I much prefer the simple approach,
as it makes using the audit data so much simpler.
Roy Harvey
Beacon Falls, CT
On 5 Jul 2006 16:12:54 -0700, sreedhardasi@.gmail.com wrote:

>Hi,
>We have a table similar to the following schema.
>Product table
> product_id int,
> product_name varchar(50),
> product_desc varchar(100),
> product_price money,
> product_sub_category_id int
>We also have tables for product category and sub category. We would
>like to save audit trail information whenever a new product is added,
>changed name, description or price, and moved to another sub category
>or category. We need to save modified user, modification datetime and
>some other audit info as well. It seems there are two options available
>saving this data i.e. either using a large table that has all audit
>columns or using field definition table. The field definition table has
>a field id and audit field column and the audit table will have
>product_id, modified field id, new value, old value, modified user and
>modification date.
>My question is which design is efficient? It seems the first is pretty
>straight forward and easy to implement, but stores redundant
>information and grows quickly. The second solution seems to reduce
>redundancy, but end up saving multiple rows if there are multiple
>updates at one time. Also, we need to make join to the same table when
>we write complex queries.
>Any comments or suggestions would be appreciated.
>Thanks,|||Hi
I agree with Roy , you will be benefit from having price's table . Much
easier to audit instead of having triggers or something else to track the
info
<sreedhardasi@.gmail.com> wrote in message
news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...
> Hi,
> We have a table similar to the following schema.
> Product table
> product_id int,
> product_name varchar(50),
> product_desc varchar(100),
> product_price money,
> product_sub_category_id int
> We also have tables for product category and sub category. We would
> like to save audit trail information whenever a new product is added,
> changed name, description or price, and moved to another sub category
> or category. We need to save modified user, modification datetime and
> some other audit info as well. It seems there are two options available
> saving this data i.e. either using a large table that has all audit
> columns or using field definition table. The field definition table has
> a field id and audit field column and the audit table will have
> product_id, modified field id, new value, old value, modified user and
> modification date.
> My question is which design is efficient? It seems the first is pretty
> straight forward and easy to implement, but stores redundant
> information and grows quickly. The second solution seems to reduce
> redundancy, but end up saving multiple rows if there are multiple
> updates at one time. Also, we need to make join to the same table when
> we write complex queries.
> Any comments or suggestions would be appreciated.
> Thanks,
>|||Thanks a lot for your suggestions. I think it is a good idea to have a
price table.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> I agree with Roy , you will be benefit from having price's table . Much
> easier to audit instead of having triggers or something else to track the
> info
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...|||Thanks a lot for your suggestions. I think it is a good idea to have a
price table.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> I agree with Roy , you will be benefit from having price's table . Much
> easier to audit instead of having triggers or something else to track the
> info
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...

Database design question - Using Field definition table

Hi,
We have a table similar to the following schema.
Product table
product_id int,
product_name varchar(50),
product_desc varchar(100),
product_price money,
product_sub_category_id int
We also have tables for product category and sub category. We would
like to save audit trail information whenever a new product is added,
changed name, description or price, and moved to another sub category
or category. We need to save modified user, modification datetime and
some other audit info as well. It seems there are two options available
saving this data i.e. either using a large table that has all audit
columns or using field definition table. The field definition table has
a field id and audit field column and the audit table will have
product_id, modified field id, new value, old value, modified user and
modification date.
My question is which design is efficient? It seems the first is pretty
straight forward and easy to implement, but stores redundant
information and grows quickly. The second solution seems to reduce
redundancy, but end up saving multiple rows if there are multiple
updates at one time. Also, we need to make join to the same table when
we write complex queries.
Any comments or suggestions would be appreciated.
Thanks,It sounds like you already understand the tradeoffs pretty well, at
least between the alternatives given.
Not knowing the details of your operation makes it hard to give
meaningful advice. Just looking at the given information, I have to
suspect that the most common change would be to product_price. It
also makes me wonder if the price does not belong in a price table
instead, with effective dates and who-changed-it-when information. In
that configuration the price table itself becomes its own audit table.
If changes to the rest of the columns are infrequent enough then that
would allow the simple approach of duplicating the entire row for the
other columns. When space permits I much prefer the simple approach,
as it makes using the audit data so much simpler.
Roy Harvey
Beacon Falls, CT
On 5 Jul 2006 16:12:54 -0700, sreedhardasi@.gmail.com wrote:
>Hi,
>We have a table similar to the following schema.
>Product table
> product_id int,
> product_name varchar(50),
> product_desc varchar(100),
> product_price money,
> product_sub_category_id int
>We also have tables for product category and sub category. We would
>like to save audit trail information whenever a new product is added,
>changed name, description or price, and moved to another sub category
>or category. We need to save modified user, modification datetime and
>some other audit info as well. It seems there are two options available
>saving this data i.e. either using a large table that has all audit
>columns or using field definition table. The field definition table has
>a field id and audit field column and the audit table will have
>product_id, modified field id, new value, old value, modified user and
>modification date.
>My question is which design is efficient? It seems the first is pretty
>straight forward and easy to implement, but stores redundant
>information and grows quickly. The second solution seems to reduce
>redundancy, but end up saving multiple rows if there are multiple
>updates at one time. Also, we need to make join to the same table when
>we write complex queries.
>Any comments or suggestions would be appreciated.
>Thanks,|||Hi
I agree with Roy , you will be benefit from having price's table . Much
easier to audit instead of having triggers or something else to track the
info
<sreedhardasi@.gmail.com> wrote in message
news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...
> Hi,
> We have a table similar to the following schema.
> Product table
> product_id int,
> product_name varchar(50),
> product_desc varchar(100),
> product_price money,
> product_sub_category_id int
> We also have tables for product category and sub category. We would
> like to save audit trail information whenever a new product is added,
> changed name, description or price, and moved to another sub category
> or category. We need to save modified user, modification datetime and
> some other audit info as well. It seems there are two options available
> saving this data i.e. either using a large table that has all audit
> columns or using field definition table. The field definition table has
> a field id and audit field column and the audit table will have
> product_id, modified field id, new value, old value, modified user and
> modification date.
> My question is which design is efficient? It seems the first is pretty
> straight forward and easy to implement, but stores redundant
> information and grows quickly. The second solution seems to reduce
> redundancy, but end up saving multiple rows if there are multiple
> updates at one time. Also, we need to make join to the same table when
> we write complex queries.
> Any comments or suggestions would be appreciated.
> Thanks,
>|||Thanks a lot for your suggestions. I think it is a good idea to have a
price table.
Uri Dimant wrote:
> Hi
> I agree with Roy , you will be benefit from having price's table . Much
> easier to audit instead of having triggers or something else to track the
> info
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...
> > Hi,
> >
> > We have a table similar to the following schema.
> >
> > Product table
> > product_id int,
> > product_name varchar(50),
> > product_desc varchar(100),
> > product_price money,
> > product_sub_category_id int
> >
> > We also have tables for product category and sub category. We would
> > like to save audit trail information whenever a new product is added,
> > changed name, description or price, and moved to another sub category
> > or category. We need to save modified user, modification datetime and
> > some other audit info as well. It seems there are two options available
> > saving this data i.e. either using a large table that has all audit
> > columns or using field definition table. The field definition table has
> > a field id and audit field column and the audit table will have
> > product_id, modified field id, new value, old value, modified user and
> > modification date.
> >
> > My question is which design is efficient? It seems the first is pretty
> > straight forward and easy to implement, but stores redundant
> > information and grows quickly. The second solution seems to reduce
> > redundancy, but end up saving multiple rows if there are multiple
> > updates at one time. Also, we need to make join to the same table when
> > we write complex queries.
> >
> > Any comments or suggestions would be appreciated.
> >
> > Thanks,
> >|||Thanks a lot for your suggestions. I think it is a good idea to have a
price table.
Uri Dimant wrote:
> Hi
> I agree with Roy , you will be benefit from having price's table . Much
> easier to audit instead of having triggers or something else to track the
> info
>
> <sreedhardasi@.gmail.com> wrote in message
> news:1152141174.756626.230500@.v61g2000cwv.googlegroups.com...
> > Hi,
> >
> > We have a table similar to the following schema.
> >
> > Product table
> > product_id int,
> > product_name varchar(50),
> > product_desc varchar(100),
> > product_price money,
> > product_sub_category_id int
> >
> > We also have tables for product category and sub category. We would
> > like to save audit trail information whenever a new product is added,
> > changed name, description or price, and moved to another sub category
> > or category. We need to save modified user, modification datetime and
> > some other audit info as well. It seems there are two options available
> > saving this data i.e. either using a large table that has all audit
> > columns or using field definition table. The field definition table has
> > a field id and audit field column and the audit table will have
> > product_id, modified field id, new value, old value, modified user and
> > modification date.
> >
> > My question is which design is efficient? It seems the first is pretty
> > straight forward and easy to implement, but stores redundant
> > information and grows quickly. The second solution seems to reduce
> > redundancy, but end up saving multiple rows if there are multiple
> > updates at one time. Also, we need to make join to the same table when
> > we write complex queries.
> >
> > Any comments or suggestions would be appreciated.
> >
> > Thanks,
> >

Database definitions

I am going to start a database and need to know the difference between
Data Modeling, Schema and Database design?

I always thought of Data Modeling and schema as defining relationships
and primary and secondary key?

What is mean when someone designs an E-R diagram and a Data Flow
Diagram??Data Modeling is the process of creating a description of the
relationships between data elements of interest. When data modeling
for relational databases, your description usually identifies tables,
columns, and foreign key relationships. The "description" usually
involves pictorial representation, e.g., boxes and arrows.

A logical data model is sometimes developed before the physical model.
The logical model is perhaps not as normalized as the physical, and
usually does not describe indexes or other performance-related
considerations. This model is most useful for communicating with
non-dba's who are interested in the data content of a system but don't
care about the physical storage considerations.

A physical data model more closely resembles the way that the data will
be actually stored in the db. It may specify indexes.

A Entity-Relationship diagram is the most common pictorial
representation of a data model, logical or physical. It's just a way
of showing the model with boxes and arrows and some special symbols
(e.g., 0..1 or 0..n) . A picture is worth a thousand words.

The schema is the actually definition of the data model in the dbms.
When you take your data model and start creating tables, columns,
indexes, and fkeys, you are creating the schema.

Of course, the data model is a representation of the schema that you
intend to create, so the terms are sometimes used interchangably. As
you show the data model to someone, you might say "here's the schema
design" or you might just shorten that to say "here's the schema". Not
quite correct, but good enough.

So, in summary, Data Modeling is an activity that results in a Data
Model. An ERD is one way of representing a data model. The data model
is used to create the databases's schema.

A data flow diagram is something entirely different. It is a pictorial
representation of how data is transformed as it moves through
processing steps of an application. For example, a data flow diagram
may show a authorization for a credit purchase as input. In an early
processing step, it will be matched against a database of account
holders. Then the purchase amount will be compared to the balance.
etc.

Hope this helps.|||THANKS!!
lit@.twcny.rr.com wrote:
> Data Modeling is the process of creating a description of the
> relationships between data elements of interest. When data modeling
> for relational databases, your description usually identifies tables,
> columns, and foreign key relationships. The "description" usually
> involves pictorial representation, e.g., boxes and arrows.
> A logical data model is sometimes developed before the physical
model.
> The logical model is perhaps not as normalized as the physical, and
> usually does not describe indexes or other performance-related
> considerations. This model is most useful for communicating with
> non-dba's who are interested in the data content of a system but
don't
> care about the physical storage considerations.
> A physical data model more closely resembles the way that the data
will
> be actually stored in the db. It may specify indexes.
> A Entity-Relationship diagram is the most common pictorial
> representation of a data model, logical or physical. It's just a way
> of showing the model with boxes and arrows and some special symbols
> (e.g., 0..1 or 0..n) . A picture is worth a thousand words.
> The schema is the actually definition of the data model in the dbms.
> When you take your data model and start creating tables, columns,
> indexes, and fkeys, you are creating the schema.
> Of course, the data model is a representation of the schema that you
> intend to create, so the terms are sometimes used interchangably. As
> you show the data model to someone, you might say "here's the schema
> design" or you might just shorten that to say "here's the schema".
Not
> quite correct, but good enough.
> So, in summary, Data Modeling is an activity that results in a Data
> Model. An ERD is one way of representing a data model. The data
model
> is used to create the databases's schema.
>
> A data flow diagram is something entirely different. It is a
pictorial
> representation of how data is transformed as it moves through
> processing steps of an application. For example, a data flow diagram
> may show a authorization for a credit purchase as input. In an early
> processing step, it will be matched against a database of account
> holders. Then the purchase amount will be compared to the balance.
> etc.
> Hope this helps.