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 deign problem

Hello all,
I have a datbase design problem
I have a hierarchy that includes 5 levels and each level have a table
EX :
TABLE_L1
L1_ID INT AUTO
L1_CODE nvarchar(50)
L1_NAME nvarchar(255)
TABLE_L2
L2_ID INT AUTO
L1_ID INT
L2_CODE nvarchar(50)
L2_NAME nvarchar(255)
etc
The primary key is an id auto. (can be replaced by a GUID if it is
necessary)
The problem :
I have a user table and must affect rights on some members than can be a
different level of the hierarchy.
For example :
User 1 can access to the member A of level one and all the level A
children's but he can also access to member B4 of level 2
I try to implement integrity so when a member is deleted all rights are
deleted too.
My first design is to have one security definition table per level but i
think i am not the first person to have to give rights on different levels
of a hierarchy and they're must be a "best practice" to design it!
anoyone knows an "ideal" solution?
Thanks
cymryr
Hi,
Typically, if I want to design a hierarchy that has more than 2 levels, I
use a parent-child relationship like that:
[OBJECT]
OBJECT_ID int auto
OBJECT_CODE nvarchar(50)
OBJECT_NAME nvarchar(255)
PARENT_OBJECT_ID int
[OBJECT_PERMISSION]
OBJECT_ID int
USER_ID int
Tomasz B.
"Cymryr" wrote:

> Hello all,
> I have a datbase design problem
> I have a hierarchy that includes 5 levels and each level have a table
> EX :
> TABLE_L1
> L1_ID INT AUTO
> L1_CODE nvarchar(50)
> L1_NAME nvarchar(255)
> TABLE_L2
> L2_ID INT AUTO
> L1_ID INT
> L2_CODE nvarchar(50)
> L2_NAME nvarchar(255)
> etc
> The primary key is an id auto. (can be replaced by a GUID if it is
> necessary)
>
> The problem :
> I have a user table and must affect rights on some members than can be a
> different level of the hierarchy.
> For example :
> User 1 can access to the member A of level one and all the level A
> children's but he can also access to member B4 of level 2
> I try to implement integrity so when a member is deleted all rights are
> deleted too.
> My first design is to have one security definition table per level but i
> think i am not the first person to have to give rights on different levels
> of a hierarchy and they're must be a "best practice" to design it!
> anoyone knows an "ideal" solution?
> Thanks
> cymryr
>
>
|||parent child have too many problems :
1/Must implement recursivity (bad performance)
2/hard to know the level of the member
3/ impossible to have different columns at each level
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...[vbcol=seagreen]
> Hi,
> Typically, if I want to design a hierarchy that has more than 2 levels, I
> use a parent-child relationship like that:
> [OBJECT]
> OBJECT_ID int auto
> OBJECT_CODE nvarchar(50)
> OBJECT_NAME nvarchar(255)
> PARENT_OBJECT_ID int
> [OBJECT_PERMISSION]
> OBJECT_ID int
> USER_ID int
> Tomasz B.
> "Cymryr" wrote:
levels[vbcol=seagreen]
|||Hi,
Of course, you have right, but please remember that, this is a dimension
table and usually it contains much less records than a fact table, so
performance is not an issue – SQL Server can load whole table into memory,
also you should expect minimal insert, update and delete activities.
If you really new a level number, you can define a column called LEVEL and
maintenance this information by trigger
If you want different columns at each level, you can define a view.
Tomasz B.
"Cymryr" wrote:

> parent child have too many problems :
> 1/Must implement recursivity (bad performance)
> 2/hard to know the level of the member
> 3/ impossible to have different columns at each level
> "Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
> message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...
> levels
>
>
|||Rather than a seperate table for each table, consider one self-referencing
table.
"Cymryr" <Cymryr@.hotmail.com> wrote in message
news:evfi5ucDFHA.3888@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have a datbase design problem
> I have a hierarchy that includes 5 levels and each level have a table
> EX :
> TABLE_L1
> L1_ID INT AUTO
> L1_CODE nvarchar(50)
> L1_NAME nvarchar(255)
> TABLE_L2
> L2_ID INT AUTO
> L1_ID INT
> L2_CODE nvarchar(50)
> L2_NAME nvarchar(255)
> etc
> The primary key is an id auto. (can be replaced by a GUID if it is
> necessary)
>
> The problem :
> I have a user table and must affect rights on some members than can be a
> different level of the hierarchy.
> For example :
> User 1 can access to the member A of level one and all the level A
> children's but he can also access to member B4 of level 2
> I try to implement integrity so when a member is deleted all rights are
> deleted too.
> My first design is to have one security definition table per level but i
> think i am not the first person to have to give rights on different levels
> of a hierarchy and they're must be a "best practice" to design it!
> anoyone knows an "ideal" solution?
> Thanks
> cymryr
>

Database deign problem

Hello all,
I have a datbase design problem
I have a hierarchy that includes 5 levels and each level have a table
EX :
TABLE_L1
L1_ID INT AUTO
L1_CODE nvarchar(50)
L1_NAME nvarchar(255)
TABLE_L2
L2_ID INT AUTO
L1_ID INT
L2_CODE nvarchar(50)
L2_NAME nvarchar(255)
etc
The primary key is an id auto. (can be replaced by a GUID if it is
necessary)
The problem :
I have a user table and must affect rights on some members than can be a
different level of the hierarchy.
For example :
User 1 can access to the member A of level one and all the level A
children's but he can also access to member B4 of level 2
I try to implement integrity so when a member is deleted all rights are
deleted too.
My first design is to have one security definition table per level but i
think i am not the first person to have to give rights on different levels
of a hierarchy and they're must be a "best practice" to design it!
anoyone knows an "ideal" solution?
Thanks
cymryrHi,
Typically, if I want to design a hierarchy that has more than 2 levels, I
use a parent-child relationship like that:
[OBJECT]
OBJECT_ID int auto
OBJECT_CODE nvarchar(50)
OBJECT_NAME nvarchar(255)
PARENT_OBJECT_ID int
[OBJECT_PERMISSION]
OBJECT_ID int
USER_ID int
Tomasz B.
"Cymryr" wrote:

> Hello all,
> I have a datbase design problem
> I have a hierarchy that includes 5 levels and each level have a table
> EX :
> TABLE_L1
> L1_ID INT AUTO
> L1_CODE nvarchar(50)
> L1_NAME nvarchar(255)
> TABLE_L2
> L2_ID INT AUTO
> L1_ID INT
> L2_CODE nvarchar(50)
> L2_NAME nvarchar(255)
> etc
> The primary key is an id auto. (can be replaced by a GUID if it is
> necessary)
>
> The problem :
> I have a user table and must affect rights on some members than can be a
> different level of the hierarchy.
> For example :
> User 1 can access to the member A of level one and all the level A
> children's but he can also access to member B4 of level 2
> I try to implement integrity so when a member is deleted all rights are
> deleted too.
> My first design is to have one security definition table per level but i
> think i am not the first person to have to give rights on different levels
> of a hierarchy and they're must be a "best practice" to design it!
> anoyone knows an "ideal" solution?
> Thanks
> cymryr
>
>|||parent child have too many problems :
1/Must implement recursivity (bad performance)
2/hard to know the level of the member
3/ impossible to have different columns at each level
"Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...[vbcol=seagreen]
> Hi,
> Typically, if I want to design a hierarchy that has more than 2 levels, I
> use a parent-child relationship like that:
> [OBJECT]
> OBJECT_ID int auto
> OBJECT_CODE nvarchar(50)
> OBJECT_NAME nvarchar(255)
> PARENT_OBJECT_ID int
> [OBJECT_PERMISSION]
> OBJECT_ID int
> USER_ID int
> Tomasz B.
> "Cymryr" wrote:
>
levels[vbcol=seagreen]|||Hi,
Of course, you have right, but please remember that, this is a dimension
table and usually it contains much less records than a fact table, so
performance is not an issue – SQL Server can load whole table into memory,
also you should expect minimal insert, update and delete activities.
If you really new a level number, you can define a column called LEVEL and
maintenance this information by trigger
If you want different columns at each level, you can define a view.
Tomasz B.
"Cymryr" wrote:

> parent child have too many problems :
> 1/Must implement recursivity (bad performance)
> 2/hard to know the level of the member
> 3/ impossible to have different columns at each level
> "Tomasz Borawski" <TomaszBorawski@.discussions.microsoft.com> wrote in
> message news:E0686F35-3BF7-43BB-A091-921652AF52BB@.microsoft.com...
> levels
>
>|||Rather than a seperate table for each table, consider one self-referencing
table.
"Cymryr" <Cymryr@.hotmail.com> wrote in message
news:evfi5ucDFHA.3888@.TK2MSFTNGP09.phx.gbl...
> Hello all,
> I have a datbase design problem
> I have a hierarchy that includes 5 levels and each level have a table
> EX :
> TABLE_L1
> L1_ID INT AUTO
> L1_CODE nvarchar(50)
> L1_NAME nvarchar(255)
> TABLE_L2
> L2_ID INT AUTO
> L1_ID INT
> L2_CODE nvarchar(50)
> L2_NAME nvarchar(255)
> etc
> The primary key is an id auto. (can be replaced by a GUID if it is
> necessary)
>
> The problem :
> I have a user table and must affect rights on some members than can be a
> different level of the hierarchy.
> For example :
> User 1 can access to the member A of level one and all the level A
> children's but he can also access to member B4 of level 2
> I try to implement integrity so when a member is deleted all rights are
> deleted too.
> My first design is to have one security definition table per level but i
> think i am not the first person to have to give rights on different levels
> of a hierarchy and they're must be a "best practice" to design it!
> anoyone knows an "ideal" solution?
> Thanks
> cymryr
>