Hello,
I have a situation where I have a table that may depend on no, one, or
multiple rows of another table for a single Transaction ID, and I'm not sure
how to design it.
Here is a simpler version of my schema:
CREATE TABLE MyTransactionTable
(
ID INTEGER IDENTITY NOT NULL,
Name VARCHAR(40),
SomeData INTEGER,
TransType INTEGER,
CONSTRAINT PK_TransTable PRIMARY KEY(ID),
CONSTRAINT FK_TransTableType(TransType) REFERENCES TransactionTypes(ID),
CONSTRAINT FK_SomeDataTable(ID) REFERENCES SomeDataTable(ID)
)
CREATE TABLE TransactionTypes
(
ID INTEGER IDENTITY NOT NULL,
Description VARCHAR(20) NOT NULL,
Priority INTEGER DEFAULT 0,
CONSTRAINT PK_TransactionTypes PRIMARY KEY (ID)
)
CREATE TABLE SomeDataTable
(
ID INTEGER IDENTITY NOT NULL,
SomeData VARCHAR(50) NOT NULL,
CONSTRAINT PK_SomeDataTable PRIMARY KEY (ID)
)
The problem is, I want a single row in MyTransactionTable that references 0,
1, or more rows in the SomeDataTable.
Thanks,
PAGatesWhy do you "...want a single row in MyTransactionTable that references 0, 1,
or more rows in the SomeDataTable"? That's not a normalized design.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:722D2991-CAC4-4F02-BBAE-DDCFF1DE763C@.microsoft.com...
Hello,
I have a situation where I have a table that may depend on no, one, or
multiple rows of another table for a single Transaction ID, and I'm not sure
how to design it.
Here is a simpler version of my schema:
CREATE TABLE MyTransactionTable
(
ID INTEGER IDENTITY NOT NULL,
Name VARCHAR(40),
SomeData INTEGER,
TransType INTEGER,
CONSTRAINT PK_TransTable PRIMARY KEY(ID),
CONSTRAINT FK_TransTableType(TransType) REFERENCES TransactionTypes(ID),
CONSTRAINT FK_SomeDataTable(ID) REFERENCES SomeDataTable(ID)
)
CREATE TABLE TransactionTypes
(
ID INTEGER IDENTITY NOT NULL,
Description VARCHAR(20) NOT NULL,
Priority INTEGER DEFAULT 0,
CONSTRAINT PK_TransactionTypes PRIMARY KEY (ID)
)
CREATE TABLE SomeDataTable
(
ID INTEGER IDENTITY NOT NULL,
SomeData VARCHAR(50) NOT NULL,
CONSTRAINT PK_SomeDataTable PRIMARY KEY (ID)
)
The problem is, I want a single row in MyTransactionTable that references 0,
1, or more rows in the SomeDataTable.
Thanks,
PAGates|||pagates wrote:
> CREATE TABLE SomeDataTable
> (
> ID INTEGER IDENTITY NOT NULL,
> SomeData VARCHAR(50) NOT NULL,
> CONSTRAINT PK_SomeDataTable PRIMARY KEY (ID)
> )
> The problem is, I want a single row in MyTransactionTable that references
0,
> 1, or more rows in the SomeDataTable.
>
Add the Transaction ID to SomeDataTable as a foreign key.
As specified your table design is weak because you don't have any
alternate keys. IDENTITY is not a uniqueness constraint and it
shouldn't be the only key of a table.
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
--|||First you need relational keys instead of IDENTITY columns. You also
need to read a book on data modeling, so you will quit using vague data
element names. I will make a guess that transactions are really keyed
by a timestamp and a user id of some kind.
CREATE TABLE MyTransactions
(trans_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
user_name VARCHAR(40) NOT NULL,
foobar_id INTEGER NOT NULL
REFERENCES SomeData(foobar_id),
trans_type INTEGER NOT NULL
REFERENCES TransactionTypes(trans_type),
PRIMARY KEY (trans_timestamp, user_name)
);
CREATE TABLE TransactionTypes
(trans_type INTEGER NOT NULL PRIMARY KEY,
trans_type_description VARCHAR(20) NOT NULL,
trans_priority INTEGER DEFAULT 0 NOT NULL
CHECK (trans_priority >= 0));
CREATE TABLE SomeData
(foobar_id INTEGER NOT NULL PRIMARY KEY,
vague_stuff VARCHAR(50) NOT NULL,
.);
1, or more rows in the SomeDataTable. <<
Then you need a table with this relationship. Here is one way to keep
the cardinality of the relation in the 1 to 3 range:
CREATE TABLE SomeData
(trans_timestamp DATETIME NOT NULL,
user_name VARCHAR(40),
FOREIGN KEY (trans_timestamp, user_name)
REFERENCES MyTransactions (trans_timestamp, user_name)
ON DELETE CASCADE
ON UPDATE CASCADE,
foobar_id INTEGER NOT NULL
references SomeData(foobar_id),
seq_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (seq_nbr IN (1, 2, 3)),
PRIMARY KEY (trans_timestamp, user_name, seq_nbr));
Showing posts with label keys. Show all posts
Showing posts with label keys. Show all posts
Sunday, February 19, 2012
Design Question - Primary Keys
Hello All,
I am in the midst of designing a database, and I have a very general
(pseudo-newbie) question.
Should all tables have a Primary Key defined?
As I understand it, there are two reasons to have a primary key:
- It is used in conjunction with a Foreign Key on a table, and
- It automatically creates an Index on a table
I would guess that most tables fit one or both of the criteria listed above,
but is it good practice for all tables, or is it unneeded overhead? For
instance, I will have a few tables that are small (maybe 50 rows, max) and
generally fixed (the data is permanent, or nearly permanent, and won't
change), and probably won't be accessed in a multi-table query.
Thanks,
pagates"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed
> above,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
You forgot the most important reason.
...whose values uniquely identify each row in the table and enforces the
entity integrity of the table.
Some would argue that a table without a primary key is not even a table.
Why would you need such a table?|||Hi Raymond,
> You forgot the most important reason.
> ...whose values uniquely identify each row in the table and enforces the
> entity integrity of the table.
> Some would argue that a table without a primary key is not even a table.
> Why would you need such a table?
Good point. I guess I was thinking of the case of a small table (2 or 3
columns) of relatively small size and constant data, and where the
combination of data on all columns comprises the uniqueness of the table.
For instance, a table that consists of "header information": maybe a company
name, and an "EstablishedIn" date. Maybe this is kept for a web site
provider company to generate some header information on a page for its
customers. Assuming that this is a small company that has only a few
customers (and plans to stay this way), this is a small table with constant
data, and the combo of CompanyName and EstablishedIn date will always be
unique.
(OK, perhaps not the best example, but the first thing that comes to mind.)
Mind you, it was my "gut feeling" that tables should always define primary
keys, but I didn't know if there was a reason not to (for instance,
overhead), especially if it was known that all rows are unique, and that the
uniqueness is the combination of all columns of a table.
Thanks,
pagates
"Raymond D'Anjou" wrote:
> "pagates" <pagates@.discussions.microsoft.com> wrote in message
> news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> You forgot the most important reason.
> ...whose values uniquely identify each row in the table and enforces the
> entity integrity of the table.
> Some would argue that a table without a primary key is not even a table.
> Why would you need such a table?
>
>|||pagates wrote:
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed abov
e,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
If the data in your small tables is truly fixed and unchanging then
there is no reason NOT to add a primary key. Keys = integrity. One day
you WILL update those tables and how many things will break if you get
duplicates in there because you didn't bother to declare the key?
Another reason: keys are useful metadata for those who will have to
support and develop your system in future and for those who will
utilise the data. Leaving out keys tells them only that you didn't know
or didn't care what infromation the table was supposed to represent. If
you are learning then start learning good habits now.
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
--|||> Mind you, it was my "gut feeling" that tables should always define primary
> keys, but I didn't know if there was a reason not to (for instance,
> overhead), especially if it was known that all rows are unique, and that
> the
> uniqueness is the combination of all columns of a table.
Once you decide what information you need to store in a database, as a
database designer your focus should change to how to keep garbage out. It
doesn't matter how the database performs if the answers it returns are
incorrect. Integrity constraints are extremely important. Murphy's Law
applies to databases, too: if it can happen, it will. You must make sure
that it can't. The only way to do this is to apply constraints to the
database, but that's not enough. You should also normalize and
orthoganalize the database to minimize, or if possible, to eliminate
redundancy. Redundancy cedes the database's constraint checking
responsibility to the application, so it's extremely important to minimize
it. You should also be careful with your queries: many use WITH(NOLOCK) to
improve performance, but as I've posted several times on this forum, NOLOCK
will make your queries return incorrect results at lightning speed. If
performance is a problem, add hardware, add indexes--add both, but don't
compromise integrity.
Whenever I come across a database without constraints, I have to spend days,
if not w
s, cleaning out the garbage. A poorly designed database can
double or even triple the amount of time needed to develop an application,
and even then, because there's no way to be sure that the database doesn't
contain garbage, it's much, much more difficult to test and troubleshoot
application code. A poor foundation can turn a two-month project into a
two-year project.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:67F5B67A-629E-45E0-A130-DB0582754000@.microsoft.com...
> Hi Raymond,
>
> Good point. I guess I was thinking of the case of a small table (2 or 3
> columns) of relatively small size and constant data, and where the
> combination of data on all columns comprises the uniqueness of the table.
> For instance, a table that consists of "header information": maybe a
> company
> name, and an "EstablishedIn" date. Maybe this is kept for a web site
> provider company to generate some header information on a page for its
> customers. Assuming that this is a small company that has only a few
> customers (and plans to stay this way), this is a small table with
> constant
> data, and the combo of CompanyName and EstablishedIn date will always be
> unique.
> (OK, perhaps not the best example, but the first thing that comes to
> mind.)
> Mind you, it was my "gut feeling" that tables should always define primary
> keys, but I didn't know if there was a reason not to (for instance,
> overhead), especially if it was known that all rows are unique, and that
> the
> uniqueness is the combination of all columns of a table.
> Thanks,
> pagates
> "Raymond D'Anjou" wrote:
>|||I see no reason why any table would not have a unique key, even if it's just
the datetime that the row was inserted.
The real debate is whether or not natural keys or an additional integer
based surrogate key (ex: identity column) should enfore foreign key
relationships.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed
> above,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
I am in the midst of designing a database, and I have a very general
(pseudo-newbie) question.
Should all tables have a Primary Key defined?
As I understand it, there are two reasons to have a primary key:
- It is used in conjunction with a Foreign Key on a table, and
- It automatically creates an Index on a table
I would guess that most tables fit one or both of the criteria listed above,
but is it good practice for all tables, or is it unneeded overhead? For
instance, I will have a few tables that are small (maybe 50 rows, max) and
generally fixed (the data is permanent, or nearly permanent, and won't
change), and probably won't be accessed in a multi-table query.
Thanks,
pagates"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed
> above,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
You forgot the most important reason.
...whose values uniquely identify each row in the table and enforces the
entity integrity of the table.
Some would argue that a table without a primary key is not even a table.
Why would you need such a table?|||Hi Raymond,
> You forgot the most important reason.
> ...whose values uniquely identify each row in the table and enforces the
> entity integrity of the table.
> Some would argue that a table without a primary key is not even a table.
> Why would you need such a table?
Good point. I guess I was thinking of the case of a small table (2 or 3
columns) of relatively small size and constant data, and where the
combination of data on all columns comprises the uniqueness of the table.
For instance, a table that consists of "header information": maybe a company
name, and an "EstablishedIn" date. Maybe this is kept for a web site
provider company to generate some header information on a page for its
customers. Assuming that this is a small company that has only a few
customers (and plans to stay this way), this is a small table with constant
data, and the combo of CompanyName and EstablishedIn date will always be
unique.
(OK, perhaps not the best example, but the first thing that comes to mind.)
Mind you, it was my "gut feeling" that tables should always define primary
keys, but I didn't know if there was a reason not to (for instance,
overhead), especially if it was known that all rows are unique, and that the
uniqueness is the combination of all columns of a table.
Thanks,
pagates
"Raymond D'Anjou" wrote:
> "pagates" <pagates@.discussions.microsoft.com> wrote in message
> news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> You forgot the most important reason.
> ...whose values uniquely identify each row in the table and enforces the
> entity integrity of the table.
> Some would argue that a table without a primary key is not even a table.
> Why would you need such a table?
>
>|||pagates wrote:
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed abov
e,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
If the data in your small tables is truly fixed and unchanging then
there is no reason NOT to add a primary key. Keys = integrity. One day
you WILL update those tables and how many things will break if you get
duplicates in there because you didn't bother to declare the key?
Another reason: keys are useful metadata for those who will have to
support and develop your system in future and for those who will
utilise the data. Leaving out keys tells them only that you didn't know
or didn't care what infromation the table was supposed to represent. If
you are learning then start learning good habits now.
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
--|||> Mind you, it was my "gut feeling" that tables should always define primary
> keys, but I didn't know if there was a reason not to (for instance,
> overhead), especially if it was known that all rows are unique, and that
> the
> uniqueness is the combination of all columns of a table.
Once you decide what information you need to store in a database, as a
database designer your focus should change to how to keep garbage out. It
doesn't matter how the database performs if the answers it returns are
incorrect. Integrity constraints are extremely important. Murphy's Law
applies to databases, too: if it can happen, it will. You must make sure
that it can't. The only way to do this is to apply constraints to the
database, but that's not enough. You should also normalize and
orthoganalize the database to minimize, or if possible, to eliminate
redundancy. Redundancy cedes the database's constraint checking
responsibility to the application, so it's extremely important to minimize
it. You should also be careful with your queries: many use WITH(NOLOCK) to
improve performance, but as I've posted several times on this forum, NOLOCK
will make your queries return incorrect results at lightning speed. If
performance is a problem, add hardware, add indexes--add both, but don't
compromise integrity.
Whenever I come across a database without constraints, I have to spend days,
if not w
s, cleaning out the garbage. A poorly designed database candouble or even triple the amount of time needed to develop an application,
and even then, because there's no way to be sure that the database doesn't
contain garbage, it's much, much more difficult to test and troubleshoot
application code. A poor foundation can turn a two-month project into a
two-year project.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:67F5B67A-629E-45E0-A130-DB0582754000@.microsoft.com...
> Hi Raymond,
>
> Good point. I guess I was thinking of the case of a small table (2 or 3
> columns) of relatively small size and constant data, and where the
> combination of data on all columns comprises the uniqueness of the table.
> For instance, a table that consists of "header information": maybe a
> company
> name, and an "EstablishedIn" date. Maybe this is kept for a web site
> provider company to generate some header information on a page for its
> customers. Assuming that this is a small company that has only a few
> customers (and plans to stay this way), this is a small table with
> constant
> data, and the combo of CompanyName and EstablishedIn date will always be
> unique.
> (OK, perhaps not the best example, but the first thing that comes to
> mind.)
> Mind you, it was my "gut feeling" that tables should always define primary
> keys, but I didn't know if there was a reason not to (for instance,
> overhead), especially if it was known that all rows are unique, and that
> the
> uniqueness is the combination of all columns of a table.
> Thanks,
> pagates
> "Raymond D'Anjou" wrote:
>|||I see no reason why any table would not have a unique key, even if it's just
the datetime that the row was inserted.
The real debate is whether or not natural keys or an additional integer
based surrogate key (ex: identity column) should enfore foreign key
relationships.
"pagates" <pagates@.discussions.microsoft.com> wrote in message
news:5F8D4C00-28F8-4DE9-884D-7300F7469329@.microsoft.com...
> Hello All,
> I am in the midst of designing a database, and I have a very general
> (pseudo-newbie) question.
> Should all tables have a Primary Key defined?
> As I understand it, there are two reasons to have a primary key:
> - It is used in conjunction with a Foreign Key on a table, and
> - It automatically creates an Index on a table
> I would guess that most tables fit one or both of the criteria listed
> above,
> but is it good practice for all tables, or is it unneeded overhead? For
> instance, I will have a few tables that are small (maybe 50 rows, max) and
> generally fixed (the data is permanent, or nearly permanent, and won't
> change), and probably won't be accessed in a multi-table query.
> Thanks,
> pagates
Design question - Foreign key indexes
A rather elementary design question - is it a good idea to index foreign keys in fact tables? I understand this will slow down inserts/deletes/updates, but when the cube is processing will it speed up processing time? Does the cube generate all dimension members then query fact tables for them? Does it query fact tables with group by's on participating dimensions? I see example cubes that have this, but am unable to find documentation saying this is a good idea. I would hate to miss out on a big performance boost, but don't want to take the chance to slow things down... Any suggestions / supporting documentation is very much welcomed.
Thanks in advance,
John Hennesey
It is an interesting question.
General assumption is Analysis Services cubes are being built based on the relational data warehouse which data is coming from OLTP system. The relationa data warehouse is not the place where you expecting lots of individual atomic updates. That is in theory. In practice depending on the size I have seen implmentations where cubes are built based on raw OLTP shemas.
It is dependant on your case and it is really tradeoff you will be making. If you would like to speed up processind of cubes , you should go and run, for example index tuning wizard and build your relational database indexes that match the queries Analysis Server sends during processing. I wouldnt go and try analyzing every query by yourself. Index tuning wizard makes work easier for you.
But if you see indexed built slowing down your ETL update process, you can always drop these indexes.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Hi:
I think there is another consideration and it is performance -not on insertion of data - but at ETL time, when you want to determine if a given row is already present in a dimension (locating a row).
Most of the times, I just index for ETL speed. OLAP Processing , because of the amount of rows read, may easily go to be a table/partition scan, making indexes almost useless.However, that also depends on the cardinality of your attributes, and what kind of processing you use in your OLAP structures
Alejandro Leguizamo
SQL Server MVP
Colombia
|||Alejandro / Edward -
Thank you very much for your quick responses - both very good points. Edward - to clarify, we are using service broker which receives xml messages from our source system, which uses stored procs to update our data warehouse. During peak times we receive hundreds of thousands of messages each day, and each one will cause updates / inserts / deletes in our dimension and fact tables. Hence my consideration about index changes slowing things down. When we process, we are doing a full process, so Alejandro's response about doing full table scans is a very realistic possibility. The only way to truly tell what is going on and if there can be some performance boost is to run the index tuning wizard - a very good suggestion. I will check it out!
Thanks again,
John
p.s. Alejandro - you would be suprised what the Enterprise Reporting solution has evolved into - I believe Robert Skoglund gave you guys a nice presentation a few months back...
Friday, February 17, 2012
Design issue please help
Hi all,
I do have four combination key for eg:1111-1111-1111-1111
the values can range from 0-0, based upon this i can create any number
of keys eg:1234-4567-4352-6534, these four values comes from different
tables, while joining the four i insert the new combination in a new
table .
In order to give security, i can give access rights to each parts for
eg: a user can have rights to 1234, but dont have the rights to 4567.
Iam using these keys in lot of my records , but i need to give a
checking based on the user , if he does not have the rights to a
particular segment , he wont be able to view , nor edit the record.
How do i implement this security on segment based,
Thanks in advance
Cheers
thomsonthomson
Look, I don't know your business requirements and obviously it depends on
bit permit me to suggest something
Please look at application role implemented by SQL Server as well as visit
on Vyas's greate web site to read below article
<http://vyaskn.tripod.com/sql_server...t_practices.htm> --se
curity best practices
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1114056973.285659.247590@.g14g2000cwa.googlegroups.com...
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Thomson,
The same problem i faced in my project and we have implemented in the
project manually[no supported my MS SQL].
For segment based manual security U need to have a separate table for this
which will let U know the which Key is accced by which User
KeyVal UserID SecurityLevel(RoleID)
1234 1 1
1234 3 2
6534 1 4
... ... ...
... ... ...
... ... ...
Hee SecurityLevel(RoleID) field has the ID/Level for Security.Security Level
may be like enum... or as u wish.
I think From this table you help you in getting the user ID with their
rights and role for a desired Keyval.
Am i clear to you ?
Please let me know.
NB: Please reply me on rakesh.ranjan@.3i-infotech.com
Thanking you
Rakesh Ranjan
rakesh.ranjan@.3i-infotech.com
Mumbai[India]
"thomson" wrote:
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Rakesh,
I think you got my point would you please explain it to me
the role of KeyVal and the Security Role id in Detail
Regards
thomson
RakeshRanjan wrote:
> Dear Thomson,
> The same problem i faced in my project and we have implemented in the
> project manually[no supported my MS SQL].
> For segment based manual security U need to have a separate table for
this
> which will let U know the which Key is accced by which User
> KeyVal UserID SecurityLevel(RoleID)
> 1234 1 1
> 1234 3 2
> 6534 1 4
> ... ... ...
> ... ... ...
> ... ... ...
> Hee SecurityLevel(RoleID) field has the ID/Level for
Security.Security Level
> may be like enum... or as u wish.
>
> I think From this table you help you in getting the user ID with
their
> rights and role for a desired Keyval.
> Am i clear to you ?
> Please let me know.
> NB: Please reply me on rakesh.ranjan@.3i-infotech.com
> Thanking you
> Rakesh Ranjan
> rakesh.ranjan@.3i-infotech.com
> Mumbai[India]
>
> "thomson" wrote:
>
number
different
new
for
4567.|||One more thing is that If you have mentioned the KeyVal value, as the
one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
between segments, or there can be any other Characters in between the
segments, So how will i tackle this
Regards
thomson|||Hi Tjhomson,
Key val is nothing but ur primary Key of all other table which make ur newly
generated Segmentkey(1234-4567-4352-6534).
You can have any charecter other than "-", It depends on you, how you want
to program it. This will be treated as a charecter for spliting your segment
key (which u have created from the four IDs).
Here the SecurityLevel(RoleID) field is rthe ID(Primary Key) of ur
security/role table which a user has, so that u can come to know the level o
f
user security.
Hope this much help u in better understanding. For further clarification
mail me on rakesh.ranjan@.3i-infotech.com
With regards,
Rakesh.
"thomson" wrote:
> Dear Rakesh,
> I think you got my point would you please explain it to me
> the role of KeyVal and the Security Role id in Detail
> Regards
> thomson
> RakeshRanjan wrote:
>
> this
> Security.Security Level
> their
> number
> different
> new
> for
> 4567.
>|||Hi rakesh,
The problem lies here that i dont want to give the security
to the whole key 1111-1111-1111-1111.
My issue is i have to give the security to each segments eg:1111
this 4 key can be generated by any one, its a one time setup, Then i
have to give security to each segments for each user.
suppose if i create a segment 1111-2222-3333-4444.
If i dont have the access to 1111, what i should not able to access the
records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
have access to the particular segment 1111.
Thanks in advance
thomson
thomson wrote:
> One more thing is that If you have mentioned the KeyVal value, as the
> one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
> between segments, or there can be any other Characters in between the
> segments, So how will i tackle this
> Regards
> thomson|||Hello Thomson,
Its little bit different than what I have Implemented. Though no issue , I
have a clue.
As u said the segment "1111-2222-3333-4444" is one time creation. Even
multiple time , I have no issue at all.
What u need to do is, after creation of the key "1111-2222-3333-4444" split
it into four parts i.e. 1111, 2222, 3333, 4444 and then Insert four records
in the said table with their rights( as i explained earlier), Now you know
which key has what access. so can can proceed further as per ur requirement.
For further please chat with me on mail "rakesh.ranjan@.3i-infotech.com".
With Regards,
Rakesh R.
"thomson" wrote:
> Hi rakesh,
> The problem lies here that i dont want to give the security
> to the whole key 1111-1111-1111-1111.
> My issue is i have to give the security to each segments eg:1111
> this 4 key can be generated by any one, its a one time setup, Then i
> have to give security to each segments for each user.
> suppose if i create a segment 1111-2222-3333-4444.
> If i dont have the access to 1111, what i should not able to access the
> records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
> have access to the particular segment 1111.
> Thanks in advance
>
> thomson
> thomson wrote:
>|||thomson,
You'll have to parse the key. If it's just 4 4-character pieces, it's
not hard.
select key from tableOfKeys
where not exists (
select deniedPermission from Permissions
where Permissions.theUser = @.theUser
and deniedPermission in (
substring(key,1,4),
substring(key,6,4),
substring(key,11,4),
substring(key,16,4)
)
)
If permissions are assigned positively, it's something like
select key from tableOfKeys
where not exists (
select keyPermission from (
select substring(key,1,4) union all
select substring(key,6,4) union all
select substring(key,11,4) union all
select substring(key,16,4)
) as K(keyPermission)
where keyPermission not in (
select permission from Permissions
where permission.theUser = @.theUser
)
)
[both untested]
Steve Kass
Drew University
thomson wrote:
>Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
>the values can range from 0-0, based upon this i can create any number
>of keys eg:1234-4567-4352-6534, these four values comes from different
>tables, while joining the four i insert the new combination in a new
>table .
>
> In order to give security, i can give access rights to each parts for
>eg: a user can have rights to 1234, but dont have the rights to 4567.
>Iam using these keys in lot of my records , but i need to give a
>checking based on the user , if he does not have the rights to a
>particular segment , he wont be able to view , nor edit the record.
>
>How do i implement this security on segment based,
>Thanks in advance
>Cheers
>thomson
>
>
I do have four combination key for eg:1111-1111-1111-1111
the values can range from 0-0, based upon this i can create any number
of keys eg:1234-4567-4352-6534, these four values comes from different
tables, while joining the four i insert the new combination in a new
table .
In order to give security, i can give access rights to each parts for
eg: a user can have rights to 1234, but dont have the rights to 4567.
Iam using these keys in lot of my records , but i need to give a
checking based on the user , if he does not have the rights to a
particular segment , he wont be able to view , nor edit the record.
How do i implement this security on segment based,
Thanks in advance
Cheers
thomsonthomson
Look, I don't know your business requirements and obviously it depends on
bit permit me to suggest something
Please look at application role implemented by SQL Server as well as visit
on Vyas's greate web site to read below article
<http://vyaskn.tripod.com/sql_server...t_practices.htm> --se
curity best practices
"thomson" <saintthomson@.yahoo.com> wrote in message
news:1114056973.285659.247590@.g14g2000cwa.googlegroups.com...
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Thomson,
The same problem i faced in my project and we have implemented in the
project manually[no supported my MS SQL].
For segment based manual security U need to have a separate table for this
which will let U know the which Key is accced by which User
KeyVal UserID SecurityLevel(RoleID)
1234 1 1
1234 3 2
6534 1 4
... ... ...
... ... ...
... ... ...
Hee SecurityLevel(RoleID) field has the ID/Level for Security.Security Level
may be like enum... or as u wish.
I think From this table you help you in getting the user ID with their
rights and role for a desired Keyval.
Am i clear to you ?
Please let me know.
NB: Please reply me on rakesh.ranjan@.3i-infotech.com
Thanking you
Rakesh Ranjan
rakesh.ranjan@.3i-infotech.com
Mumbai[India]
"thomson" wrote:
> Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
> the values can range from 0-0, based upon this i can create any number
> of keys eg:1234-4567-4352-6534, these four values comes from different
> tables, while joining the four i insert the new combination in a new
> table .
>
> In order to give security, i can give access rights to each parts for
> eg: a user can have rights to 1234, but dont have the rights to 4567.
> Iam using these keys in lot of my records , but i need to give a
> checking based on the user , if he does not have the rights to a
> particular segment , he wont be able to view , nor edit the record.
>
> How do i implement this security on segment based,
> Thanks in advance
> Cheers
> thomson
>|||Dear Rakesh,
I think you got my point would you please explain it to me
the role of KeyVal and the Security Role id in Detail
Regards
thomson
RakeshRanjan wrote:
> Dear Thomson,
> The same problem i faced in my project and we have implemented in the
> project manually[no supported my MS SQL].
> For segment based manual security U need to have a separate table for
this
> which will let U know the which Key is accced by which User
> KeyVal UserID SecurityLevel(RoleID)
> 1234 1 1
> 1234 3 2
> 6534 1 4
> ... ... ...
> ... ... ...
> ... ... ...
> Hee SecurityLevel(RoleID) field has the ID/Level for
Security.Security Level
> may be like enum... or as u wish.
>
> I think From this table you help you in getting the user ID with
their
> rights and role for a desired Keyval.
> Am i clear to you ?
> Please let me know.
> NB: Please reply me on rakesh.ranjan@.3i-infotech.com
> Thanking you
> Rakesh Ranjan
> rakesh.ranjan@.3i-infotech.com
> Mumbai[India]
>
> "thomson" wrote:
>
number
different
new
for
4567.|||One more thing is that If you have mentioned the KeyVal value, as the
one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
between segments, or there can be any other Characters in between the
segments, So how will i tackle this
Regards
thomson|||Hi Tjhomson,
Key val is nothing but ur primary Key of all other table which make ur newly
generated Segmentkey(1234-4567-4352-6534).
You can have any charecter other than "-", It depends on you, how you want
to program it. This will be treated as a charecter for spliting your segment
key (which u have created from the four IDs).
Here the SecurityLevel(RoleID) field is rthe ID(Primary Key) of ur
security/role table which a user has, so that u can come to know the level o
f
user security.
Hope this much help u in better understanding. For further clarification
mail me on rakesh.ranjan@.3i-infotech.com
With regards,
Rakesh.
"thomson" wrote:
> Dear Rakesh,
> I think you got my point would you please explain it to me
> the role of KeyVal and the Security Role id in Detail
> Regards
> thomson
> RakeshRanjan wrote:
>
> this
> Security.Security Level
> their
> number
> different
> new
> for
> 4567.
>|||Hi rakesh,
The problem lies here that i dont want to give the security
to the whole key 1111-1111-1111-1111.
My issue is i have to give the security to each segments eg:1111
this 4 key can be generated by any one, its a one time setup, Then i
have to give security to each segments for each user.
suppose if i create a segment 1111-2222-3333-4444.
If i dont have the access to 1111, what i should not able to access the
records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
have access to the particular segment 1111.
Thanks in advance
thomson
thomson wrote:
> One more thing is that If you have mentioned the KeyVal value, as the
> one like 1111-1111-1111-1111, Then the issue is there can be a Hypen
> between segments, or there can be any other Characters in between the
> segments, So how will i tackle this
> Regards
> thomson|||Hello Thomson,
Its little bit different than what I have Implemented. Though no issue , I
have a clue.
As u said the segment "1111-2222-3333-4444" is one time creation. Even
multiple time , I have no issue at all.
What u need to do is, after creation of the key "1111-2222-3333-4444" split
it into four parts i.e. 1111, 2222, 3333, 4444 and then Insert four records
in the said table with their rights( as i explained earlier), Now you know
which key has what access. so can can proceed further as per ur requirement.
For further please chat with me on mail "rakesh.ranjan@.3i-infotech.com".
With Regards,
Rakesh R.
"thomson" wrote:
> Hi rakesh,
> The problem lies here that i dont want to give the security
> to the whole key 1111-1111-1111-1111.
> My issue is i have to give the security to each segments eg:1111
> this 4 key can be generated by any one, its a one time setup, Then i
> have to give security to each segments for each user.
> suppose if i create a segment 1111-2222-3333-4444.
> If i dont have the access to 1111, what i should not able to access the
> records having 1111-2222-3333-4444 or 2222-1111-3333-4444, cos i dont
> have access to the particular segment 1111.
> Thanks in advance
>
> thomson
> thomson wrote:
>|||thomson,
You'll have to parse the key. If it's just 4 4-character pieces, it's
not hard.
select key from tableOfKeys
where not exists (
select deniedPermission from Permissions
where Permissions.theUser = @.theUser
and deniedPermission in (
substring(key,1,4),
substring(key,6,4),
substring(key,11,4),
substring(key,16,4)
)
)
If permissions are assigned positively, it's something like
select key from tableOfKeys
where not exists (
select keyPermission from (
select substring(key,1,4) union all
select substring(key,6,4) union all
select substring(key,11,4) union all
select substring(key,16,4)
) as K(keyPermission)
where keyPermission not in (
select permission from Permissions
where permission.theUser = @.theUser
)
)
[both untested]
Steve Kass
Drew University
thomson wrote:
>Hi all,
> I do have four combination key for eg:1111-1111-1111-1111
>the values can range from 0-0, based upon this i can create any number
>of keys eg:1234-4567-4352-6534, these four values comes from different
>tables, while joining the four i insert the new combination in a new
>table .
>
> In order to give security, i can give access rights to each parts for
>eg: a user can have rights to 1234, but dont have the rights to 4567.
>Iam using these keys in lot of my records , but i need to give a
>checking based on the user , if he does not have the rights to a
>particular segment , he wont be able to view , nor edit the record.
>
>How do i implement this security on segment based,
>Thanks in advance
>Cheers
>thomson
>
>
Subscribe to:
Posts (Atom)