Determining Security: type

Showing posts with label type. Show all posts

Thursday, March 29, 2012

Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.
C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:

> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
>
>
|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.
|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:

> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>
|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...

> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.
|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jan

Determine top 25 rows in a table that includes a ntext data type.

We have a table that has ~900,000 rows in the table. The table previously had
~1.5 million rows of data when the ~600,000 rows where deleted the table size
shrunk ~1 G. The table is currently useing ~27 GB of storage.
I would like to write a query to determine my largest 25 rows. This is
being done to determine if the storage is truely being used.C,
Perhaps:
SELECT TOP 25 RowKey, LEN(NTextColumn)
FROM YourTable
ORDER BY LEN(NTextColumn) DESC
RLF
"C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> We have a table that has ~900,000 rows in the table. The table previously
> had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table
> size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.|||I'm attemping to run it now. I had to use DATALENGTH instead of LEN for the
NTEXT field.
I was hoping to find a was to gather the information using system tables ie
number of pages used by row.
"Russell Fields" wrote:
> C,
> Perhaps:
> SELECT TOP 25 RowKey, LEN(NTextColumn)
> FROM YourTable
> ORDER BY LEN(NTextColumn) DESC
> RLF
> "C RAMSEY" <CRAMSEY@.discussions.microsoft.com> wrote in message
> news:B84581A5-1BB6-4495-B68C-FB65E250E917@.microsoft.com...
> > We have a table that has ~900,000 rows in the table. The table previously
> > had
> > ~1.5 million rows of data when the ~600,000 rows where deleted the table
> > size
> > shrunk ~1 G. The table is currently useing ~27 GB of storage.
> >
> > I would like to write a query to determine my largest 25 rows. This is
> > being done to determine if the storage is truely being used.
>
>|||> I was hoping to find a was to gather the information using system tables
> ie
> number of pages used by row.
No system table stores this information... there might be some undocumented
DBCC command that does it, but even that would probably be coming from the
page angle, not the row angle.|||That's what I was affraid of. I used DBCC IND to get the page information.
The other technique was taking to long to run so I stopped it.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > I was hoping to find a was to gather the information using system tables
> > ie
> > number of pages used by row.
> No system table stores this information... there might be some undocumented
> DBCC command that does it, but even that would probably be coming from the
> page angle, not the row angle.
>
>|||> That's what I was affraid of. I used DBCC IND to get the page
> information.
Did you figure out the top 25 rows already? If not...
> The other technique was taking to long to run so I stopped it.
...maybe you could try doing this in two steps, e.g.
SELECT pk, tcl = DATALENGTH(text_col) INTO #foo FROM main_table;
SELECT TOP 25 pk, tcl FROM #foo ORDER BY tcl DESC;
This might be a lot cheaper then including the datalength() calculation and
the ordering in the same step.|||C RAMSEY wrote:
> We have a table that has ~900,000 rows in the table. The table previously had
> ~1.5 million rows of data when the ~600,000 rows where deleted the table size
> shrunk ~1 G. The table is currently useing ~27 GB of storage.
> I would like to write a query to determine my largest 25 rows. This is
> being done to determine if the storage is truely being used.
When you delete rows, the space that was occupied might not be released.
It might be retained as "unused space", and reused for future rows.
If it is your goal to lower the used storage, then you could first run
sp_spaceused to determine the amount of unused space and/or DBCC
SHOWCONTIG to determine the average page density. If there is a lot of
unused space, then make sure you have a clustered index (or add one) and
run DBCC INDEXDEFRAG or DBCC DBREINDEX / ALTER TABLE to remove the
unused space.
HTH,
Gert-Jansql

Determine the type of trigger

I would like to know how to determine what type of trigger is occurring -
I am getting syntax errors with the following code
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
If delete then
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),'Delete', INSERTED.* FROM Inserted
End If
If insert then
INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
CURRENT_USER),'Insert', INSERTED.* FROM Inserted
End If
If update then
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),'Update', INSERTED.* FROM Inserted
End If
ENDThe inserteD and deleteD (note the D on the end) virtual tables used inside
of triggered represent changes made to the table the trigger is 'ON' by a
single transaction. For a transaction that is an INSERT, the inserted virtua
l
table will contain the new rows to be added (the deleted virtual table will
be empty). For a transaction that is a DELETE, the deleted virtual table wil
l
have the rows to be removed (the inserted virtual table will be empty). For
a
transaction that is an UPDATE, the deleted virtual table will have the old
(current) values, and the inserted virtual table will have the new values
specified by the update.
Your code fails because you can't use the keywords
'insert','delete','update' in a boolean expression like you did.
You need something like this
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @.insertedcount int
DECLARE @.deletedcount int
SELECT @.insertedcount = COUNT(*) FROM inserted
SELECT @.deletedcount = COUNT(*) FROM deleted
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER),
CASE
WHEN @.insertedcount = 0 THEN 'DELETE'
WHEN @.deletedcount = 0 THEN 'INSERT'
ELSE 'UPDATE'
END,
INSERTED.* FROM Inserted
END--
"CSHARPITPRO" wrote:

> I would like to know how to determine what type of trigger is occurring -
> I am getting syntax errors with the following code
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
>
> BEGIN
> If delete then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Delete', INSERTED.* FROM Inserted
> End If
> If insert then
> INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
> CURRENT_USER),'Insert', INSERTED.* FROM Inserted
> End If
> If update then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Update', INSERTED.* FROM Inserted
> End If
> END
>|||if this is the actual code, then the problem is in the "if delete |
insert | update then" lines. There is no such statement.
[There is an UPDATE() function that is available inside a trigger to
determine if a particular column has updated rows, but that would be
different than the attempted statement(s)]
Also, in addition to the inserted virtual table, there is a deleted
virtual table that is present for updates and deletes.
Look up CREATE TRIGGER in BOL for more details on using these.
there's no error if you try to access these virtual table and they
aren't there, so you can have a single insert
you will need to explicitly name your columns, instead of using inserted.*
also, this example assumes, as in the op, that only the modified rows'
values are being put into the audit table.
e.g.
create trigger [trgTest] on [dbo].[Abatements]
for insert, update, delete
as
begin
insert into AuditAbatements (<col names here> )
select getdate(), convert(char(30), current_user),
case when inserted.pk_col is null then 'Delete'
when deleted.pk_col is null then 'Insert'
else 'Update' end,
isnull(inserted.column1, deleted.column1),...etc...,
isnull(inserted.columnN, deleted.columnN)
from inserted
full join deleted on inserted.pk_col = deleted.pk_col
end
CSHARPITPRO wrote:
> I would like to know how to determine what type of trigger is occurring -
> I am getting syntax errors with the following code
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
>
> BEGIN
> If delete then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Delete', INSERTED.* FROM Inserted
> End If
> If insert then
> INSERT INTO AuditAbatements SELECT GETDATE(),convert(char(30),
> CURRENT_USER),'Insert', INSERTED.* FROM Inserted
> End If
> If update then
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),'Update', INSERTED.* FROM Inserted
> End If
> END
>|||Thanks Mark,
You have really helped me today!
"Mark Williams" wrote:
> The inserteD and deleteD (note the D on the end) virtual tables used insid
e
> of triggered represent changes made to the table the trigger is 'ON' by a
> single transaction. For a transaction that is an INSERT, the inserted virt
ual
> table will contain the new rows to be added (the deleted virtual table wil
l
> be empty). For a transaction that is a DELETE, the deleted virtual table w
ill
> have the rows to be removed (the inserted virtual table will be empty). Fo
r a
> transaction that is an UPDATE, the deleted virtual table will have the old
> (current) values, and the inserted virtual table will have the new values
> specified by the update.
> Your code fails because you can't use the keywords
> 'insert','delete','update' in a boolean expression like you did.
> You need something like this
> CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
> FOR INSERT, UPDATE, DELETE
> AS
> BEGIN
> DECLARE @.insertedcount int
> DECLARE @.deletedcount int
> SELECT @.insertedcount = COUNT(*) FROM inserted
> SELECT @.deletedcount = COUNT(*) FROM deleted
> INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
> CURRENT_USER),
> CASE
> WHEN @.insertedcount = 0 THEN 'DELETE'
> WHEN @.deletedcount = 0 THEN 'INSERT'
> ELSE 'UPDATE'
> END,
> INSERTED.* FROM Inserted
> END--
>
> "CSHARPITPRO" wrote:
>|||Well, I mislead you a little bit too. There is a problem with the last
trigger that I posted. When the CASE expression evaluates to a DELETE action
,
the SELECT that results will be empty, because the inserted virtual table is
empty on DELETE transactions.
CREATE TRIGGER [trgTest] ON [dbo].[Abatements]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
DECLARE @.insertedcount int
DECLARE @.deletedcount int
SELECT @.insertedcount = COUNT(*) FROM inserted
SELECT @.deletedcount = COUNT(*) FROM deleted
IF (@.insertedcount = 0)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'DELETE', DELETED.* FROM Inserted
END
IF (@.deletedcount = 0)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'INSERT', INSERTED.* FROM Inserted
END
IF (@.insertedcount = @.deletedcount)
BEGIN
INSERT INTO AuditAbatements SELECT GETDATE(), convert(char(30),
CURRENT_USER), 'UPDATE', INSERTED.* FROM Inserted
END
END
"CSHARPITPRO" wrote:
> Thanks Mark,
> You have really helped me today!
> "Mark Williams" wrote:
>

Saturday, February 25, 2012

designing for encryption ...

In light of SARBOX, ChoicePoint security breaches, identity theft etc, what
are some best practices regarding encryption and security of this type of
info? I know this is a pretty broad question, so links to other sites are
great, but I'd like to hear some personal experiences and opinions.
The reason is I have identified what I consider potential security problems
in our systems. I need to get get a sense of how critical these particular
issues are (I tend to take the position that hyper vigilence is best so for
me everything is critical) and get them in front of management to hopefully
get some action. But I always think it's best to have at least a proposal
for a solution when presenting a problem.
Anyway, any feedback is most appreciated.
Bob Castleman
DBA PoseurHere is a little something from a mind far greater than mine...
http://vyaskn.tripod.com/sql_server...t_practices.htm
Peter
"The length of this document defends it well against the risk of its being
read."
Winston Churchill
"Bob Castleman" wrote:

> In light of SARBOX, ChoicePoint security breaches, identity theft etc, wha
t
> are some best practices regarding encryption and security of this type of
> info? I know this is a pretty broad question, so links to other sites are
> great, but I'd like to hear some personal experiences and opinions.
> The reason is I have identified what I consider potential security problem
s
> in our systems. I need to get get a sense of how critical these particular
> issues are (I tend to take the position that hyper vigilence is best so fo
r
> me everything is critical) and get them in front of management to hopefull
y
> get some action. But I always think it's best to have at least a proposal
> for a solution when presenting a problem.
> Anyway, any feedback is most appreciated.
> Bob Castleman
> DBA Poseur
>
>|||SARBOX is not an area I'm expert in but one thing I know about security
is that it isn't the same as encryption. Encryption is just one tool
for security. So "designing for security" is something different from
"designing for encryption" and I would advise you to focus on the
former rather than the latter. As I understand it SARBOX does NOT
mandate that any data be encrypted it just requires "adequate" internal
controls.
David Portas
SQL Server MVP
--|||I believe that Sarbanes Oxley suggests or recommends all "Sensitive" data be
encrypted in a data store.
SSNs
Account Numbers
Visa Numbers
etc etc etc
I could be wrong (I'm not a SOX Audit expert either)
Greg Jackson
PDX, Oregon|||Not a lawyer or security expert but California law, SB 1386
(http://info.sen.ca.gov/pub/01-02/bi...86_bill_2002092
6_chaptered.html) mentions unencrypted data. It seems that if someone where
to breach your database and personal information was encrypted you would not
need to disclose the breach.
See [url]http://informationw

.com/story/showArticle.jhtml?articleID=10700814[/url]
for an overview...
"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:%23Jf3G$8LFHA.576@.TK2MSFTNGP15.phx.gbl...
> I believe that Sarbanes Oxley suggests or recommends all "Sensitive" data
be
> encrypted in a data store.
> SSNs
> Account Numbers
> Visa Numbers
> etc etc etc
> I could be wrong (I'm not a SOX Audit expert either)
> Greg Jackson
> PDX, Oregon
>

Friday, February 24, 2012

Design type question

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

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

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

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

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

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

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

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

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

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

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

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

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

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

That's my 2 cents :P

Peace,

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

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

Design Question: column storing a type

Hi,
I'm 50/50 about this design topic, could someone please shed some light?
thanks!
I often have to add a column to our db just to store a permission type of a
user etc... In the code (C#), it should be enum type to UI level; in the
db, I am not sure which of these 2 ways is "generally" a better design:
1) varchar type with check constraint to make sure certain type description
can be stored.
2) int type with or without check constraint (without would allow the code
to extend the enum type without changing db)
I know that (2) is a bit faster and take less space but it takes a long time
to look up some info (imagine if we have 50 of these types through out the
system). (1) would give us a better context by run a sql statement, it would
be harder to make reading mistake and bug in stored proc because it's highly
descriptive.
Thanks!!"Zester" <zeze@.nottospam.com> wrote in message
news:O5KWfW1OIHA.1208@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm 50/50 about this design topic, could someone please shed some light?
> thanks!
> I often have to add a column to our db just to store a permission type of
> a user etc... In the code (C#), it should be enum type to UI level; in
> the db, I am not sure which of these 2 ways is "generally" a better
> design:
> 1) varchar type with check constraint to make sure certain type
> description can be stored.
> 2) int type with or without check constraint (without would allow the code
> to extend the enum type without changing db)
> I know that (2) is a bit faster and take less space but it takes a long
> time to look up some info (imagine if we have 50 of these types through
> out the system). (1) would give us a better context by run a sql
> statement, it would be harder to make reading mistake and bug in stored
> proc because it's highly descriptive.
> Thanks!!
>
Or
3) A column with a FOREIGN KEY referencing a PermissionType table.
If you are likely to modify the set of types frequently then go for 3)
because that way you can easily use the PermissionType table to drive the
options available in your app without any code change.
If you are happy to make schema and code changes whenever the set of types
changes then use 1).
Not certain what your intention is with 2). I think you mean a surrogate
key, which is a differrent question altogether and one that doesn't have a
simple answer. I suggest you consult your DBA / Database Architect.
--
David Portas|||For (2), I meant that we just stored the enum value in the form of the
integer without referencing to the definition table (which is option 3 you
pointed out). For example, in C/C# code
enum AllowPrintPermission
{
None, // never allow = 0
AllowPrintPublicForms, // = 1
AllowPrintAllForms // = 2
}
This type will be stored as 0,1,2 respectively
(3) would result in many tables for us. These types are not shared by
multiple tables.
We have lots of user permissions, it's a con to do many joints to get the
meaning, sometimes the sql statement can get so complex that data mining and
debugging tasks down the road can be a high cost.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uJuYZe1OIHA.4476@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:O5KWfW1OIHA.1208@.TK2MSFTNGP05.phx.gbl...
>> Hi,
>> I'm 50/50 about this design topic, could someone please shed some light?
>> thanks!
>> I often have to add a column to our db just to store a permission type of
>> a user etc... In the code (C#), it should be enum type to UI level; in
>> the db, I am not sure which of these 2 ways is "generally" a better
>> design:
>> 1) varchar type with check constraint to make sure certain type
>> description can be stored.
>> 2) int type with or without check constraint (without would allow the
>> code to extend the enum type without changing db)
>> I know that (2) is a bit faster and take less space but it takes a long
>> time to look up some info (imagine if we have 50 of these types through
>> out the system). (1) would give us a better context by run a sql
>> statement, it would be harder to make reading mistake and bug in stored
>> proc because it's highly descriptive.
>> Thanks!!
> Or
> 3) A column with a FOREIGN KEY referencing a PermissionType table.
> If you are likely to modify the set of types frequently then go for 3)
> because that way you can easily use the PermissionType table to drive the
> options available in your app without any code change.
> If you are happy to make schema and code changes whenever the set of types
> changes then use 1).
> Not certain what your intention is with 2). I think you mean a surrogate
> key, which is a differrent question altogether and one that doesn't have a
> simple answer. I suggest you consult your DBA / Database Architect.
> --
> David Portas
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
> For (2), I meant that we just stored the enum value in the form of the
> integer without referencing to the definition table (which is option 3 you
> pointed out). For example, in C/C# code
> enum AllowPrintPermission
> {
> None, // never allow = 0
> AllowPrintPublicForms, // = 1
> AllowPrintAllForms // = 2
> }
> This type will be stored as 0,1,2 respectively
> (3) would result in many tables for us. These types are not shared by
> multiple tables.
> We have lots of user permissions, it's a con to do many joints to get the
> meaning, sometimes the sql statement can get so complex that data mining
> and debugging tasks down the road can be a high cost.
>
If you don't mind breaking normalization rules a bit, then you can do a
modified version of 3.
In the lookup table, include an identifier of some type (TableName for
example).
Then a single PermissionType table could support many tables in your db.
Example:
CREATE TABLE dbo.PermissionType (
PermissionTypeID int IDENTITY(1,1) NOT NULL PRIMARY KEY
SchemaName sysname NOT NULL,
TableName sysname NOT NULL,
PermissionType varchar(100) NOT NULL,
PermissionTypeEnum int NOT NULL)
ALTER TABLE dbo.PermissionType ADD UNIQUE CONSTRAINT UC_PermissionType
(SchemaName, TableName, PermissionType, PermissionTypeEnum)
INSERT PermissionType VALUES ('dbo.', 'Payroll', 'ViewAll', 0)
Rick Sawtell
MCT, MCSD, MCDBA|||"Zester" <zeze@.nottospam.com> wrote in message
news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
> For (2), I meant that we just stored the enum value in the form of the
> integer without referencing to the definition table (which is option 3 you
> pointed out). For example, in C/C# code
> enum AllowPrintPermission
> {
> None, // never allow = 0
> AllowPrintPublicForms, // = 1
> AllowPrintAllForms // = 2
> }
> This type will be stored as 0,1,2 respectively
> (3) would result in many tables for us. These types are not shared by
> multiple tables.
> We have lots of user permissions, it's a con to do many joints to get the
> meaning, sometimes the sql statement can get so complex that data mining
> and debugging tasks down the road can be a high cost.
>
Creating an extra table does not mean you need any more joins or more
complex SQL than before. Use exactly the same queries you would in your
other solutions. Creating an extra table may just make it easier to maintain
the set of values. "Many tables" should not pose any kind of problem that I
can see.
Of course there is no single "right" answer. Just my 0.02
--
David Portas|||Thanks for pointing to a new direction; so what type would the column in
payroll table be? int? how does it reference (via foreign key) to the
Permission table when the value is not a primary key in Permission table?
If there is no connection via foreign key to maintain the integrity of the
relationship, I don't see the benefit of this approach. Could you please
explain? thanks!!
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> wrote in message
news:OleRx31OIHA.5524@.TK2MSFTNGP05.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
>> For (2), I meant that we just stored the enum value in the form of the
>> integer without referencing to the definition table (which is option 3
>> you pointed out). For example, in C/C# code
>> enum AllowPrintPermission
>> {
>> None, // never allow = 0
>> AllowPrintPublicForms, // = 1
>> AllowPrintAllForms // = 2
>> }
>> This type will be stored as 0,1,2 respectively
>> (3) would result in many tables for us. These types are not shared by
>> multiple tables.
>> We have lots of user permissions, it's a con to do many joints to get the
>> meaning, sometimes the sql statement can get so complex that data mining
>> and debugging tasks down the road can be a high cost.
> If you don't mind breaking normalization rules a bit, then you can do a
> modified version of 3.
> In the lookup table, include an identifier of some type (TableName for
> example).
> Then a single PermissionType table could support many tables in your db.
> Example:
> CREATE TABLE dbo.PermissionType (
> PermissionTypeID int IDENTITY(1,1) NOT NULL PRIMARY KEY
> SchemaName sysname NOT NULL,
> TableName sysname NOT NULL,
> PermissionType varchar(100) NOT NULL,
> PermissionTypeEnum int NOT NULL)
> ALTER TABLE dbo.PermissionType ADD UNIQUE CONSTRAINT UC_PermissionType
> (SchemaName, TableName, PermissionType, PermissionTypeEnum)
>
> INSERT PermissionType VALUES ('dbo.', 'Payroll', 'ViewAll', 0)
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:evdwX$1OIHA.6036@.TK2MSFTNGP03.phx.gbl...
> Thanks for pointing to a new direction; so what type would the column in
> payroll table be? int? how does it reference (via foreign key) to the
> Permission table when the value is not a primary key in Permission table?
> If there is no connection via foreign key to maintain the integrity of the
> relationship, I don't see the benefit of this approach. Could you please
> explain? thanks!!
1. Use the IDENTITY column in the base tables. You do not have to create a
FK constraint, but it is not a bad idea.
2. Perform joins on that IDENTITY column to get your enum
Rick|||Extra table would bring more joints, right? To use sql statement to find out
user permissions, we would have to do this:
Assume PrintPermission table is defined with 2 columns
PrintPermission
(
PermissionType int primary key not null default( 0 ), check PermissionType
in (0,1,2),
PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
)
select u.UserName, printPerm.PermissionDesc
from User u JOIN PrintPermission printPerm on u.PrintPermissionType =printPerm.PermissionType
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O1igq91OIHA.4912@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OU%23j2k1OIHA.2376@.TK2MSFTNGP02.phx.gbl...
>> For (2), I meant that we just stored the enum value in the form of the
>> integer without referencing to the definition table (which is option 3
>> you pointed out). For example, in C/C# code
>> enum AllowPrintPermission
>> {
>> None, // never allow = 0
>> AllowPrintPublicForms, // = 1
>> AllowPrintAllForms // = 2
>> }
>> This type will be stored as 0,1,2 respectively
>> (3) would result in many tables for us. These types are not shared by
>> multiple tables.
>> We have lots of user permissions, it's a con to do many joints to get the
>> meaning, sometimes the sql statement can get so complex that data mining
>> and debugging tasks down the road can be a high cost.
> Creating an extra table does not mean you need any more joins or more
> complex SQL than before. Use exactly the same queries you would in your
> other solutions. Creating an extra table may just make it easier to
> maintain the set of values. "Many tables" should not pose any kind of
> problem that I can see.
> Of course there is no single "right" answer. Just my 0.02
> --
> David Portas
>|||Hi Rick,
what's the benefit for this approach? FK must references a primary key, in
your suggestion, the primary key in PermissionType is PermissionTypeID
(not the possible value of the PrintPermissionType), so it's a lose
relationship. I still don't see the benefit that worth the joining troubles.
thanks!
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> wrote in message
news:efRNMH2OIHA.5980@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:evdwX$1OIHA.6036@.TK2MSFTNGP03.phx.gbl...
>> Thanks for pointing to a new direction; so what type would the column in
>> payroll table be? int? how does it reference (via foreign key) to the
>> Permission table when the value is not a primary key in Permission table?
>> If there is no connection via foreign key to maintain the integrity of
>> the relationship, I don't see the benefit of this approach. Could you
>> please explain? thanks!!
> 1. Use the IDENTITY column in the base tables. You do not have to create
> a FK constraint, but it is not a bad idea.
> 2. Perform joins on that IDENTITY column to get your enum
>
> Rick
>
>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:eGke7G2OIHA.1164@.TK2MSFTNGP02.phx.gbl...
> Extra table would bring more joints, right? To use sql statement to find
> out user permissions, we would have to do this:
> Assume PrintPermission table is defined with 2 columns
> PrintPermission
> (
> PermissionType int primary key not null default( 0 ), check
> PermissionType in (0,1,2),
> PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
> ('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
> )
> select u.UserName, printPerm.PermissionDesc
> from User u JOIN PrintPermission printPerm on u.PrintPermissionType => printPerm.PermissionType
>
Compared to what alternative? Either the description is in the database or
it isn't. If it isn't then it's irrelevant whether or not you create an
extra table. No join is necessary:
SELECT u.UserName, u.PrintPermissionType
FROM User;
If you DO want the description in the database then I don't know what
alternative you are proposing.
--
David Portas|||"Zester" <zeze@.nottospam.com> wrote in message
news:uhu1$L2OIHA.5988@.TK2MSFTNGP02.phx.gbl...
> Hi Rick,
> what's the benefit for this approach? FK must references a primary key, in
> your suggestion, the primary key in PermissionType is PermissionTypeID
> (not the possible value of the PrintPermissionType), so it's a lose
> relationship. I still don't see the benefit that worth the joining
> troubles. thanks!
My apologies.. In your primary tables, add the value in the IDENTITY column
from the PermissionType table as a FK.
Example:
CREATE dbo.SomeTableStoringData (
x int PRIMARY KEY,
y varchar(100), -- Some data
z varchar(100), -- Some data
PermissionTypeID int NOT NULL,
CONSTRAINT FK_SomeTableStoringData_PermissionType FOREIGN KEY
(PermissionTypeID) REFERENCES dbo.PermissionType (PermissionTypeID)
)
SELECT
SomeTableStoringData.x,
SomeTableStoringData.y,
PermissionType.Description,
PermissionType.PermissionTypeEnum
FROM
dbo.SomeTableStoringData
JOIN
dbo.PermissionType
ON SomeTableStoringData.PermissionTypeID = PermissionType.PermissionTypeID
-- You can include a WHERE clause to ensure that the correct table
permissions are being looked at. Example:
WHERE PermissionType.PermissionTypeEnum = 2 -- In this example, the enum 2
means ViewAll
Rick Sawtell|||Comparing with option (1); the other options including what you brought
updon't have enough benefits to offset the joining troubles. Of course,
someone outthere might have a few more pros to add to them that can tip the
scale.
Option (1) gives me the descriptions in the db with no joint. I am basically
seeking out strong arguments against it being the best approach (when no
other table would share the type).
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OhmeqN2OIHA.536@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:eGke7G2OIHA.1164@.TK2MSFTNGP02.phx.gbl...
>> Extra table would bring more joints, right? To use sql statement to find
>> out user permissions, we would have to do this:
>> Assume PrintPermission table is defined with 2 columns
>> PrintPermission
>> (
>> PermissionType int primary key not null default( 0 ), check
>> PermissionType in (0,1,2),
>> PermissionDesc varchar(50) not null default ( 'NeverAllow' ), check in
>> ('NeverAllow', 'AllowPrintPublicForms', 'AllowPrintAllForms' )
>> )
>> select u.UserName, printPerm.PermissionDesc
>> from User u JOIN PrintPermission printPerm on u.PrintPermissionType =>> printPerm.PermissionType
>>
>
> Compared to what alternative? Either the description is in the database or
> it isn't. If it isn't then it's irrelevant whether or not you create an
> extra table. No join is necessary:
> SELECT u.UserName, u.PrintPermissionType
> FROM User;
> If you DO want the description in the database then I don't know what
> alternative you are proposing.
> --
> David Portas
>|||So basically, this approach would unify all possible values of all
permission types in the system, right? PermissionTypeID can be 125 when 125
is defined as AllowPrintingPrivateForms. What about if we need to store
another permission for viewing files. That would result in another
column PermissionTypeID2. If a user in our system has 50 permission types,
it would be PermissionTypeID1...50?
Now the need for joining is even higher, right? that is because I can't just
count on my memory as much that PermissionType = 0 means no permission (the
basic default situation) since it would have a value of 124. What's the
benefit? thanks!
"Rick Sawtell" <r_sawtell@.nospam.hotmail.com> wrote in message
news:OPD1vS2OIHA.4272@.TK2MSFTNGP06.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:uhu1$L2OIHA.5988@.TK2MSFTNGP02.phx.gbl...
>> Hi Rick,
>> what's the benefit for this approach? FK must references a primary key,
>> in your suggestion, the primary key in PermissionType is PermissionTypeID
>> (not the possible value of the PrintPermissionType), so it's a lose
>> relationship. I still don't see the benefit that worth the joining
>> troubles. thanks!
>
> My apologies.. In your primary tables, add the value in the IDENTITY
> column from the PermissionType table as a FK.
> Example:
> CREATE dbo.SomeTableStoringData (
> x int PRIMARY KEY,
> y varchar(100), -- Some data
> z varchar(100), -- Some data
> PermissionTypeID int NOT NULL,
> CONSTRAINT FK_SomeTableStoringData_PermissionType FOREIGN KEY
> (PermissionTypeID) REFERENCES dbo.PermissionType (PermissionTypeID)
> )
>
> SELECT
> SomeTableStoringData.x,
> SomeTableStoringData.y,
> PermissionType.Description,
> PermissionType.PermissionTypeEnum
> FROM
> dbo.SomeTableStoringData
> JOIN
> dbo.PermissionType
> ON SomeTableStoringData.PermissionTypeID => PermissionType.PermissionTypeID
> -- You can include a WHERE clause to ensure that the correct table
> permissions are being looked at. Example:
> WHERE PermissionType.PermissionTypeEnum = 2 -- In this example, the enum
> 2 means ViewAll
>
> Rick Sawtell
>
>
>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
> Comparing with option (1); the other options including what you brought
> updon't have enough benefits to offset the joining troubles. Of course,
> someone outthere might have a few more pros to add to them that can tip
> the scale.
> Option (1) gives me the descriptions in the db with no joint. I am
> basically seeking out strong arguments against it being the best approach
> (when no other table would share the type).
>
A disadvantage of (1) is that it needs a schema change to add a new type.
The advantage of (3) is that it doesn't and it *doesn't* require any extra
joins either compared to (1). But I think I'm just failing to communicate
that second point so it's over to you from here on...
--
David Portas|||so with 50 possible permission types in a system, you would have 50 of these
tables with 2 columns each?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best approach
>> (when no other table would share the type).
> A disadvantage of (1) is that it needs a schema change to add a new type.
> The advantage of (3) is that it doesn't and it *doesn't* require any extra
> joins either compared to (1). But I think I'm just failing to communicate
> that second point so it's over to you from here on...
> --
> David Portas
>|||I totally agree with David here. These codes should each have their own
table with a foreign key constraint. You don't always have to use a
surrogate key for them either, use the name of the permission as the Key and
then you don't have to do any extra joins. If the name is too long you can
usually come up with a unique abbreviation that still conveys the meaning
and you still don't have to do the extra joins.
That said, designing a database around eliminating joins is the wrong
approach. These kind of "lookup" tables are usually pretty small so the
joins are very fast and efficient. Even if they aren't small, rather than
searching for ways to avoid the joins, you can use things such as indexed
views to speed up queries.
Don't compromise data integrity and long term viability for the sake of
saving a few joins.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best approach
>> (when no other table would share the type).
> A disadvantage of (1) is that it needs a schema change to add a new type.
> The advantage of (3) is that it doesn't and it *doesn't* require any extra
> joins either compared to (1). But I think I'm just failing to communicate
> that second point so it's over to you from here on...
> --
> David Portas
>|||Thanks for your input. Hm, that's true that we can just keeping using the
text description in the main table and just create another table to be
referenced. So you would have 50 extra tables, but why data integrity is an
issue when just use check constraint to make sure the set options are
declared and reinforced? The only drawback I see so far is if we need to add
new enum value to the set, we need to change the check constraint instead of
just simply inserting another entry in the permission type definition table.
However, to do the insertion, we need 50 UI pieces. I think there are
something I should point out, we host db solution internally (on-site,
web-based) so modifying the db constraints is an easy thing for us comparing
with releasing db schema with the solution like other product.
"DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
news:eN04BR3OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>I totally agree with David here. These codes should each have their own
>table with a foreign key constraint. You don't always have to use a
>surrogate key for them either, use the name of the permission as the Key
>and then you don't have to do any extra joins. If the name is too long you
>can usually come up with a unique abbreviation that still conveys the
>meaning and you still don't have to do the extra joins.
> That said, designing a database around eliminating joins is the wrong
> approach. These kind of "lookup" tables are usually pretty small so the
> joins are very fast and efficient. Even if they aren't small, rather than
> searching for ways to avoid the joins, you can use things such as indexed
> views to speed up queries.
> Don't compromise data integrity and long term viability for the sake of
> saving a few joins.
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> "Zester" <zeze@.nottospam.com> wrote in message
>> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best
>> approach (when no other table would share the type).
>>
>> A disadvantage of (1) is that it needs a schema change to add a new type.
>> The advantage of (3) is that it doesn't and it *doesn't* require any
>> extra joins either compared to (1). But I think I'm just failing to
>> communicate that second point so it's over to you from here on...
>> --
>> David Portas
>>
>|||Constraints work for this if the number of valid values is small and
relatively static. I still prefer the use of tables and FK's though. You
don't need to create 50 new UI pieces to update those tables, only those
that will change "frequently". I think it's easier to insert or update
tables as part of a deployment, than to change check constraints...
"Zester" <zeze@.nottospam.com> wrote in message
news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
> Thanks for your input. Hm, that's true that we can just keeping using the
> text description in the main table and just create another table to be
> referenced. So you would have 50 extra tables, but why data integrity is
> an issue when just use check constraint to make sure the set options are
> declared and reinforced? The only drawback I see so far is if we need to
> add new enum value to the set, we need to change the check constraint
> instead of just simply inserting another entry in the permission type
> definition table. However, to do the insertion, we need 50 UI pieces. I
> think there are something I should point out, we host db solution
> internally (on-site, web-based) so modifying the db constraints is an easy
> thing for us comparing with releasing db schema with the solution like
> other product.
> "DCPeterson" <sgtp_usmc@.hotmail.com> wrote in message
> news:eN04BR3OIHA.4808@.TK2MSFTNGP05.phx.gbl...
>>I totally agree with David here. These codes should each have their own
>>table with a foreign key constraint. You don't always have to use a
>>surrogate key for them either, use the name of the permission as the Key
>>and then you don't have to do any extra joins. If the name is too long
>>you can usually come up with a unique abbreviation that still conveys the
>>meaning and you still don't have to do the extra joins.
>> That said, designing a database around eliminating joins is the wrong
>> approach. These kind of "lookup" tables are usually pretty small so the
>> joins are very fast and efficient. Even if they aren't small, rather
>> than searching for ways to avoid the joins, you can use things such as
>> indexed views to speed up queries.
>> Don't compromise data integrity and long term viability for the sake of
>> saving a few joins.
>> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
>> news:%23kGdHb2OIHA.4712@.TK2MSFTNGP04.phx.gbl...
>> "Zester" <zeze@.nottospam.com> wrote in message
>> news:OwzDKV2OIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> Comparing with option (1); the other options including what you brought
>> updon't have enough benefits to offset the joining troubles. Of course,
>> someone outthere might have a few more pros to add to them that can tip
>> the scale.
>> Option (1) gives me the descriptions in the db with no joint. I am
>> basically seeking out strong arguments against it being the best
>> approach (when no other table would share the type).
>>
>> A disadvantage of (1) is that it needs a schema change to add a new
>> type. The advantage of (3) is that it doesn't and it *doesn't* require
>> any extra joins either compared to (1). But I think I'm just failing to
>> communicate that second point so it's over to you from here on...
>> --
>> David Portas
>>
>>
>|||"Zester" <zeze@.nottospam.com> wrote in message
news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
> Thanks for your input. Hm, that's true that we can just keeping using the
> text description in the main table and just create another table to be
> referenced. So you would have 50 extra tables, but why data integrity is
> an issue when just use check constraint to make sure the set options are
> declared and reinforced? The only drawback I see so far is if we need to
> add new enum value to the set, we need to change the check constraint
> instead of just simply inserting another entry in the permission type
> definition table. However, to do the insertion, we need 50 UI pieces. I
> think there are something I should point out, we host db solution
> internally (on-site, web-based) so modifying the db constraints is an easy
> thing for us comparing with releasing db schema with the solution like
> other product.
>
With a CHECK constraint how would you enumerate the set of values in your
application? If you code them in your app as well then you have to change it
in two different places and rebuild your app just to create a new value. If
you put them in a table your application can easily retrieve them directly
so you don't need to update your app each time you create a new value.
--
David Portas|||That's a good point but I think in general we don't want to make something
dynamic unnecessarily. These enum value sets can be hard-coded in the code.
I believe C# for example can convert enum value to the string (the enum
name) to match with the db. Even if we are dealing with older language, we
can have a layer to do these translations; it's far cheaper than create a UI
to do that for each additional table. Adding a new enum value is not
something should be happening frequently; it breaks switch statement and
if-else combo's very often and create bugs; so the cost of modifying the
contraint is small relative to a total cost of adding a new value because we
always detect it that we need to change it at a small amount of testing.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OXyMip3OIHA.3516@.TK2MSFTNGP02.phx.gbl...
> "Zester" <zeze@.nottospam.com> wrote in message
> news:uIE29b3OIHA.3532@.TK2MSFTNGP04.phx.gbl...
>> Thanks for your input. Hm, that's true that we can just keeping using the
>> text description in the main table and just create another table to be
>> referenced. So you would have 50 extra tables, but why data integrity is
>> an issue when just use check constraint to make sure the set options are
>> declared and reinforced? The only drawback I see so far is if we need to
>> add new enum value to the set, we need to change the check constraint
>> instead of just simply inserting another entry in the permission type
>> definition table. However, to do the insertion, we need 50 UI pieces. I
>> think there are something I should point out, we host db solution
>> internally (on-site, web-based) so modifying the db constraints is an
>> easy thing for us comparing with releasing db schema with the solution
>> like other product.
> With a CHECK constraint how would you enumerate the set of values in your
> application? If you code them in your app as well then you have to change
> it in two different places and rebuild your app just to create a new
> value. If you put them in a table your application can easily retrieve
> them directly so you don't need to update your app each time you create a
> new value.
> --
> David Portas
>

Sunday, February 19, 2012

design question

Howdy all. I have developers wanting to design a table that will hold not
only client info, but what type of program they are eligible for. I have
narrowed it down to 2 options, but was really looking for opinions. The
current design is like this:
Client Table:
ClientId
LName
FName
(Lots of other info that I will look at later)
IsCash(bit)
IsFoodStamps(bit)
IsMedical(bit)
But I'd like to split it out a bit so the various programs will be in
another table(s):
Option 1:
CLIENT TABLE:
ClientId
LName
FName
PROGRAMS TABLE:
ProgramId
ProgramName
CLIENT PROGRAM LOOKUP TABLE:
ClientId
ProgramId
Option 2:
CLIENT TABLE:
ClientId
LName
FName
CLIENT PROGRAM TABLE:
ClientId
Program (with a CHECK constraint so that only certain values can be entered)
Other ideas are welcomed as well.
TIA, ChrisR> Howdy all. I have developers wanting to design a table that will hold not
> only client info, but what type of program they are eligible for. I have
> narrowed it down to 2 options, but was really looking for opinions. The
> current design is like this:
> Client Table:
> ClientId
> LName
> FName
> (Lots of other info that I will look at later)
> IsCash(bit)
> IsFoodStamps(bit)
> IsMedical(bit)
I'm leaning toward your option 1, or this, with 1 Program = many Clients:
Program table:
ProgramID
Description
Client Table:
ClientID
LName
FName
ProgramID
I think your decision should be based on whether or not a client can belong
to one program at a time, or more than one at a time. So if I can belong to
both Cash and FoodStamps programs, this design won't work and you will want
to go with your Option 1.
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||To answer your question "yes, a client can and will belong to more than one
program". But why wouldn't Option 2 work?
CLIENT TABLE:
ClientId
LName
FName
CLIENT PROGRAM TABLE:
ClientId
Program
Insert into Client(1234,'Blow','Joe')
Insert into ClientProgram(1234, 'FoodStamps')
Insert into ClientProgram(1234, 'Medical')
I know it's probably not the proper way to do things, but would reduce the
amount of joins. Plus if I had a CHECK constraint on the column "program",
only the right info could be entered.
Thoughts?
"Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
news:OrAyv0gSGHA.4608@.tk2msftngp13.phx.gbl...
not
> I'm leaning toward your option 1, or this, with 1 Program = many Clients:
> Program table:
> ProgramID
> Description
> Client Table:
> ClientID
> LName
> FName
> ProgramID
> I think your decision should be based on whether or not a client can
belong
> to one program at a time, or more than one at a time. So if I can belong
to
> both Cash and FoodStamps programs, this design won't work and you will
want
> to go with your Option 1.
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||ChrisR wrote:
> To answer your question "yes, a client can and will belong to more than on
e
> program". But why wouldn't Option 2 work?
> CLIENT TABLE:
> ClientId
> LName
> FName
> CLIENT PROGRAM TABLE:
> ClientId
> Program
>
> Insert into Client(1234,'Blow','Joe')
> Insert into ClientProgram(1234, 'FoodStamps')
> Insert into ClientProgram(1234, 'Medical')
> I know it's probably not the proper way to do things, but would reduce the
> amount of joins. Plus if I had a CHECK constraint on the column "program",
> only the right info could be entered.
> Thoughts?
>
Two things to consider. Under option 2 the users won't be able to add
new Programs - that would require a schema change to alter the CHECK
constraint. Also, if you want to change the program description for any
reason then you'll have to change it on every row, not just once.
Perhaps option 2 will also require more storage - it appears so from
your sample data.
In general I'd say that Option 1 is better for "descriptive" text
(because business users are more likely to want changes to
descriptions), whereas Option 2 is better for sets of codes because
they tend to change less frequently.
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
--|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:eMm9AAhSGHA.4740@.TK2MSFTNGP14.phx.gbl...
> To answer your question "yes, a client can and will belong to more than
> one
> program". But why wouldn't Option 2 work?
It would work.
Option one (clients, programs, and a third table to match the two) is the
standard method for representing a many to many relationship.
Option two would work if the only information about the program that you
care about is the program name. If you plan on entering any other details
about the program, then this isn't good enough.|||I hadn't thought of either of the points you guys vrought up. Thanks a lot
and have a great w

end.
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:eMm9AAhSGHA.4740@.TK2MSFTNGP14.phx.gbl...
> To answer your question "yes, a client can and will belong to more than
one
> program". But why wouldn't Option 2 work?
> CLIENT TABLE:
> ClientId
> LName
> FName
> CLIENT PROGRAM TABLE:
> ClientId
> Program
>
> Insert into Client(1234,'Blow','Joe')
> Insert into ClientProgram(1234, 'FoodStamps')
> Insert into ClientProgram(1234, 'Medical')
> I know it's probably not the proper way to do things, but would reduce the
> amount of joins. Plus if I had a CHECK constraint on the column "program",
> only the right info could be entered.
> Thoughts?
>
> "Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
> news:OrAyv0gSGHA.4608@.tk2msftngp13.phx.gbl...
> not
have
The
Clients:
> belong
belong
> to
> want
>

Friday, February 17, 2012

design Index problem

Hi all,
I have a very large table with many columns: dateTime type, nvarchar
type and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present
too. Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in
every type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!
Some ideas?
thnxEnorme Vigenti (LSimon5@.libero.it) writes:

Quote:

Originally Posted by

I have a very large table with many columns: dateTime type, nvarchar
type and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present
too. Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in
every type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!

How selective is the datetime column? If all queries are for a single
day, maybe an index on that column is sufficient, preferrably a clustered
index.

But if queries can be for longer periods of time, that may address too many
rows, and in such case you will need to add more indexes. How these indexes
should be designed depends on the queries. If a query can be on account
number and a date interval, it's probably better to have the account number
first in that index.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If you could post an example Query ?

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com
"Enorme Vigenti" <LSimon5@.libero.itwrote in message
news:0kZSi.151009$U01.1108206@.twister1.libero.it.. .

Quote:

Originally Posted by

Hi all,
I have a very large table with many columns: dateTime type, nvarchar type
and integer field type.
A program exec many type of query with where clause.
Data field is always in where clause, but some other field is present too.
Sometimes integer field, sometimes nvarchar field.
Now I must create index for the query!
For choose index field what i can do?
I must create only index on a datetime field or every combination in every
type of query?
In the second case I must create very much index! But this is very
dispendious for update/insert/delete operation on the table!!!
Some ideas?
thnx

Tuesday, February 14, 2012

design help

hey all,
can someone please help me design the following:
9/29/05 Cut Lawn $20
Date of Service Service Type Price Payment Due Trans Type Payment
Amount Payment Date
9/29/05 Cut Lawn $20 10/29/05 Bill
9/29/05 Cut Lawn $20 10/29/05 Payment $10 10/1/2005
9/29/05 Cut Lawn $20 10/29/05 Payment $15 10/14/2005
Here's what I have so far:
Customer table
Invoice
InvoiceDetails
And here's where I need some help
Would I need a payment table? if so, how would it link to the invoice?
Also, where and how would you keep track of past due amounts and customers
who pay more than the amount billed?
thanks,
ariStart here:
http://www.databaseanswers.org/data...oices/index.htm
-oj
"ari" <ari@.discussions.microsoft.com> wrote in message
news:40BCFA96-97FC-432E-B7E8-19F78FDA7D53@.microsoft.com...
> hey all,
> can someone please help me design the following:
> 9/29/05 Cut Lawn $20
> Date of Service Service Type Price Payment Due Trans Type Payment
> Amount Payment Date
> 9/29/05 Cut Lawn $20 10/29/05 Bill
> 9/29/05 Cut Lawn $20 10/29/05 Payment $10 10/1/2005
> 9/29/05 Cut Lawn $20 10/29/05 Payment $15 10/14/2005
> Here's what I have so far:
> Customer table
> Invoice
> InvoiceDetails
> And here's where I need some help
> Would I need a payment table? if so, how would it link to the invoice?
> Also, where and how would you keep track of past due amounts and customers
> who pay more than the amount billed?
> thanks,
> ari|||Awesome.
Thanks,
ari
"oj" wrote:

> Start here:
> [url]http://www.databaseanswers.org/data_models/customers_and_invoices/index.htm[/url
]
> --
> -oj
>
> "ari" <ari@.discussions.microsoft.com> wrote in message
> news:40BCFA96-97FC-432E-B7E8-19F78FDA7D53@.microsoft.com...
>
>

Thursday, March 29, 2012

Determine top 25 rows in a table that includes a ntext data type.

Determine top 25 rows in a table that includes a ntext data type.

Determine the type of trigger

Saturday, February 25, 2012

designing for encryption ...

Friday, February 24, 2012

Design type question

Design Question: column storing a type

Design Question: column storing a type

Design Question: column storing a type

Sunday, February 19, 2012

design question

Friday, February 17, 2012

design Index problem

Tuesday, February 14, 2012

design help

Determining Security

Blog Archive

About Me