Could some one please point me to a good resource on how to design history
tables in a dataware house situation'
For example, in the case of products table, if product description got
changed over time after the product was purchased. The old invoice still
shows the old description but the table reflects the new one.
How could some one keep track of both descriptions?
What would be a good design/architecture'
TIA...Well, the invoices table should contain a relatively permanent piece of
data, such as a ProductID of some kind, not a much more flexible piece of
information like a description.
That said, it may still be useful to store the history of a product.
Probably the simplest in this very specific case would be:
CREATE TABLE dbo.ProductDescriptionHistory
(
ProductID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Products(ProductID),
Description VARCHAR(255),
EffectiveDate SMALLDATETIME
)
This will allow you to reconstruct invoices from the past, with the correct
"at the time" description, without bloating the invoices table with a big
VARCHAR that will usually be redundant.
You will probably come across the same dilemma with price... do you store
price data for products where the price may or may not change, or do you
just reference the productID?
Your exact solution will at least partially depend on some of the
information you haven't provided, such as exactly why you need the historic
descriptions, what you're going to do with them, and how often they actually
change.
"sqlster" <nospam@.nospam.com> wrote in message
news:3F41C612-B1AB-441F-AED7-26F4C7ABEA09@.microsoft.com...
> Could some one please point me to a good resource on how to design history
> tables in a dataware house situation'
> For example, in the case of products table, if product description got
> changed over time after the product was purchased. The old invoice still
> shows the old description but the table reflects the new one.
> How could some one keep track of both descriptions?
> What would be a good design/architecture'
> TIA...|||<Aaron>
Your exact solution will at least partially depend on some of the
information you haven't provided, such as exactly why you need the historic
descriptions, what you're going to do with them, and how often they actually
change.
</Aaron>
I need the historic descriptions for the reporting purposes only. Some of
the values could change 10 - 15 times a month.
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, the invoices table should contain a relatively permanent piece of
> data, such as a ProductID of some kind, not a much more flexible piece of
> information like a description.
> That said, it may still be useful to store the history of a product.
> Probably the simplest in this very specific case would be:
> CREATE TABLE dbo.ProductDescriptionHistory
> (
> ProductID INT NOT NULL
> FOREIGN KEY REFERENCES dbo.Products(ProductID),
> Description VARCHAR(255),
> EffectiveDate SMALLDATETIME
> )
> This will allow you to reconstruct invoices from the past, with the correc
t
> "at the time" description, without bloating the invoices table with a big
> VARCHAR that will usually be redundant.
> You will probably come across the same dilemma with price... do you store
> price data for products where the price may or may not change, or do you
> just reference the productID?
> Your exact solution will at least partially depend on some of the
> information you haven't provided, such as exactly why you need the histori
c
> descriptions, what you're going to do with them, and how often they actual
ly
> change.
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:3F41C612-B1AB-441F-AED7-26F4C7ABEA09@.microsoft.com...
>
>|||Keep who changed the rows and when in separate columns in the archive table.
You might also want to keep a record of whether the row in the main table wa
s
updated or deleted.
E.g.:
Main table:
Col1 : Col2 : ... : ColN
Archive table:
Col1 : Col2 : ... : ColN : ChangedDateTime : ChangedBy : ChangeType
Of course changes are propagated to the archive table via triggers on the
main table (for update and for delete).
For a more elaborate solution, please at least provide DDL.
ML
http://milambda.blogspot.com/|||ML,
I am just looking for some books/websites/articles that address good history
table design. The example that I brought up is just a hypothetical example s
o
I don't have any DDL.
TIA..
"ML" wrote:
> Keep who changed the rows and when in separate columns in the archive tabl
e.
> You might also want to keep a record of whether the row in the main table
was
> updated or deleted.
> E.g.:
> Main table:
> Col1 : Col2 : ... : ColN
> Archive table:
> Col1 : Col2 : ... : ColN : ChangedDateTime : ChangedBy : ChangeType
> Of course changes are propagated to the archive table via triggers on the
> main table (for update and for delete).
> For a more elaborate solution, please at least provide DDL.
>
> ML
> --
> http://milambda.blogspot.com/
Showing posts with label situation. Show all posts
Showing posts with label situation. Show all posts
Saturday, February 25, 2012
Sunday, February 19, 2012
Design Question - Tables/Foreign Keys with Potential Arrays of Dat
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));
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));
design question - so many columns....
Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?
On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.
|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.
|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -
|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.
|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?
On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.
|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.
|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -
|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.
|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
design question - so many columns....
Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > Got a situation where our main, core data in our main tables consist
> > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > several "sets" of booleans. Example - health risk factors....high
> > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > say. The user can choose none, all, or any combo in between in these
> > sets of booleans. Well each of those fundamentally is just a bit
> > column, with a zero or one, attached to the record concerning the
> > individual person/event record. Well what if I have 8 or 9 "sets" of
> > these boolean questions? This results in 150-200+ columns in my
> > table. I know sql can handle up to 1024, and these data really belong
> > on this record with this individual person/event.
> > I just wanted to see what others thought from the design perspective.
> > Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
> > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > Got a situation where our main, core data in our main tables consist
> > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > several "sets" of booleans. Example - health risk factors....high
> > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > say. The user can choose none, all, or any combo in between in these
> > > sets of booleans. Well each of those fundamentally is just a bit
> > > column, with a zero or one, attached to the record concerning the
> > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > these boolean questions? This results in 150-200+ columns in my
> > > table. I know sql can handle up to 1024, and these data really belong
> > > on this record with this individual person/event.
> > > I just wanted to see what others thought from the design perspective.
> > > Any suggestions?
> > That's quite easy. Let's say you have one column called 'Risk Factor'
> > that can take 20 possible values. You can store values like
> > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > would prefer this. Another solution is you can have a varchar(20) with
> > values like 10001000 etc where you set a bit for particular risk
> > factor. You have to know the ordinal position for a particular risk
> > and if that bit is set or not.
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>> Got a situation where our main, core data in our main tables consist
>> of 15-20 normal columns (dates, integers, varchar, etc.) and then
>> several "sets" of booleans. Example - health risk factors....high
>> blood pressure, diabetes, depression....up to 20 risk factors, let's
>> say. The user can choose none, all, or any combo in between in these
>> sets of booleans. Well each of those fundamentally is just a bit
>> column, with a zero or one, attached to the record concerning the
>> individual person/event record. Well what if I have 8 or 9 "sets" of
>> these boolean questions? This results in 150-200+ columns in my
>> table. I know sql can handle up to 1024, and these data really belong
>> on this record with this individual person/event.
>> I just wanted to see what others thought from the design perspective.
>> Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers => 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
> > On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> > > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > > Got a situation where our main, core data in our main tables consist
> > > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > > several "sets" of booleans. Example - health risk factors....high
> > > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > > say. The user can choose none, all, or any combo in between in these
> > > > sets of booleans. Well each of those fundamentally is just a bit
> > > > column, with a zero or one, attached to the record concerning the
> > > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > > these boolean questions? This results in 150-200+ columns in my
> > > > table. I know sql can handle up to 1024, and these data really belong
> > > > on this record with this individual person/event.
> > > > I just wanted to see what others thought from the design perspective.
> > > > Any suggestions?
> > > That's quite easy. Let's say you have one column called 'Risk Factor'
> > > that can take 20 possible values. You can store values like
> > > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > > would prefer this. Another solution is you can have a varchar(20) with
> > > values like 10001000 etc where you set a bit for particular risk
> > > factor. You have to know the ordinal position for a particular risk
> > > and if that bit is set or not.
> > and of course if you want to be relational then you have a row for
> > each 'Risk Factor'. So instead of growing the table horizontally you
> > grow them vertically. So if a person has 'Diabetes' and 'High Blood
> > Pressure' there are 2 rows for that person. Then you can group by a
> > particular 'Risk Factor'. For example, find all patients where 'Risk
> > Factor' is 'Diabetes' and sum how much they spent etc on medication
> > etc.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > Got a situation where our main, core data in our main tables consist
> > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > several "sets" of booleans. Example - health risk factors....high
> > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > say. The user can choose none, all, or any combo in between in these
> > sets of booleans. Well each of those fundamentally is just a bit
> > column, with a zero or one, attached to the record concerning the
> > individual person/event record. Well what if I have 8 or 9 "sets" of
> > these boolean questions? This results in 150-200+ columns in my
> > table. I know sql can handle up to 1024, and these data really belong
> > on this record with this individual person/event.
> > I just wanted to see what others thought from the design perspective.
> > Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
> > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > Got a situation where our main, core data in our main tables consist
> > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > several "sets" of booleans. Example - health risk factors....high
> > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > say. The user can choose none, all, or any combo in between in these
> > > sets of booleans. Well each of those fundamentally is just a bit
> > > column, with a zero or one, attached to the record concerning the
> > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > these boolean questions? This results in 150-200+ columns in my
> > > table. I know sql can handle up to 1024, and these data really belong
> > > on this record with this individual person/event.
> > > I just wanted to see what others thought from the design perspective.
> > > Any suggestions?
> > That's quite easy. Let's say you have one column called 'Risk Factor'
> > that can take 20 possible values. You can store values like
> > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > would prefer this. Another solution is you can have a varchar(20) with
> > values like 10001000 etc where you set a bit for particular risk
> > factor. You have to know the ordinal position for a particular risk
> > and if that bit is set or not.
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>> Got a situation where our main, core data in our main tables consist
>> of 15-20 normal columns (dates, integers, varchar, etc.) and then
>> several "sets" of booleans. Example - health risk factors....high
>> blood pressure, diabetes, depression....up to 20 risk factors, let's
>> say. The user can choose none, all, or any combo in between in these
>> sets of booleans. Well each of those fundamentally is just a bit
>> column, with a zero or one, attached to the record concerning the
>> individual person/event record. Well what if I have 8 or 9 "sets" of
>> these boolean questions? This results in 150-200+ columns in my
>> table. I know sql can handle up to 1024, and these data really belong
>> on this record with this individual person/event.
>> I just wanted to see what others thought from the design perspective.
>> Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers => 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
> > On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> > > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > > Got a situation where our main, core data in our main tables consist
> > > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > > several "sets" of booleans. Example - health risk factors....high
> > > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > > say. The user can choose none, all, or any combo in between in these
> > > > sets of booleans. Well each of those fundamentally is just a bit
> > > > column, with a zero or one, attached to the record concerning the
> > > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > > these boolean questions? This results in 150-200+ columns in my
> > > > table. I know sql can handle up to 1024, and these data really belong
> > > > on this record with this individual person/event.
> > > > I just wanted to see what others thought from the design perspective.
> > > > Any suggestions?
> > > That's quite easy. Let's say you have one column called 'Risk Factor'
> > > that can take 20 possible values. You can store values like
> > > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > > would prefer this. Another solution is you can have a varchar(20) with
> > > values like 10001000 etc where you set a bit for particular risk
> > > factor. You have to know the ordinal position for a particular risk
> > > and if that bit is set or not.
> > and of course if you want to be relational then you have a row for
> > each 'Risk Factor'. So instead of growing the table horizontally you
> > grow them vertically. So if a person has 'Diabetes' and 'High Blood
> > Pressure' there are 2 rows for that person. Then you can group by a
> > particular 'Risk Factor'. For example, find all patients where 'Risk
> > Factor' is 'Diabetes' and sum how much they spent etc on medication
> > etc.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
design question - so many columns....
Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.
Design Question
Hi- I have a situation where the FK to a table could come from three different tables. As an example I have TableA, TableB and TableC. TableD could be a child table with Ids from any of the first three tables. For any row, it will have the Id from only one of the above three tables. So I have two design options. First,
Table D:
TableDId,
Col1,
Col2,
TableAId,
TableBId,
TableCId
etc...
So for each record, one of three Ids would be not null.
Alternate design is
TableDId,
Col1,
Col2,
TableABCId (This could be an Id from any of the A, B, C Tables)
TableName (To specify if the above Id is from TableA, TableB or TableC).
Which of the above two designs do you recommend. And if there is a third, better option, what would that be?
Thanks a lot for your time.The first design is MUCH better. You can declare foreign key constraints to enforce the integrity between table D and tables A, B, and C. If you really need it, you can create a CHECK constraint to insure that only one of the three FK values is NOT NULL, but I wouldn't do that because it has always bitten me in the past (there is always an exception, and sometimes the baseline rules change to allow more than one non-null value).
-PatP
Table D:
TableDId,
Col1,
Col2,
TableAId,
TableBId,
TableCId
etc...
So for each record, one of three Ids would be not null.
Alternate design is
TableDId,
Col1,
Col2,
TableABCId (This could be an Id from any of the A, B, C Tables)
TableName (To specify if the above Id is from TableA, TableB or TableC).
Which of the above two designs do you recommend. And if there is a third, better option, what would that be?
Thanks a lot for your time.The first design is MUCH better. You can declare foreign key constraints to enforce the integrity between table D and tables A, B, and C. If you really need it, you can create a CHECK constraint to insure that only one of the three FK values is NOT NULL, but I wouldn't do that because it has always bitten me in the past (there is always an exception, and sometimes the baseline rules change to allow more than one non-null value).
-PatP
Friday, February 17, 2012
Design help please.....
Hello,
I am a newbie and I need to know if I am creating this database properly.
Here is the situation. I have three main components to the database: A user, a report and a profile. Basically it is a security database deciding the access rights to the report defined by the updatable profile.
Here is how I have created the database:
USERS
----
UID [FK]
PWD
First_Name
Last_Name
Profile_Id
PROFILES
----
Profile_Id [PK]
Profile_Desc
REPORTS
----
Report_Id
Report_Desc
PROFILE_REPORTS_LINK
----------
Index [PK]
Profile_Id [PK]
Report_Id [PK]
REPORT_ACCESS
-------
Index
Access_Selections
If I start off with a UID, my app. would need to retrieve the access_selections based upon the current report selected and the profile_id previously defined by which user is logged in. I hope this makes sense... I would like to know if there is a better way of doing this and if I am to choose this way, how do I update the PROFILE_REPORTS_LINK table every time I add a new profile or a new report?
Thanks in advance.
MORI0043Is it 1-to-1 relationship between USERS and PROFILES? Is UID supposed to be a PK, not FK, and Profile_Id in USERS to be FK? And in PROFILE_REPORTS_LINK Index to stay PK while all other the fields listed are supposed to be FK, not PK, right? And REPORT_ACCESS.Index to be FK? Lastly, can a profile belong to more than one user, which means it's not 1-to-1, but rather 0/1-to-many relationship between PROFILES and USERS? If the answer is YES to all the above but the first one, - then I have one more questions, - what is the purpose of PROFILES table? Unless some additional info is present, - you can easily remove PROFILES and put UID in place of Profile_Id into PROFILE_REPORTS_LINK. Of course, this is only if I am not completely off target :)|||-Sorry... Mistype. UID is a PK.
-Yes it is a 1 to 1 relationship between USERS and PROFILES
-Sorry again.. I was going too fast. Yes in the PROFILE_LINK table both IDs are [FK]s
-You can have a profile for 0-many users... Basically it is a group profile where there would be administrators group and general group and so forth
-I don't think that you remove PROFILES because this table defines the profile which can exist for 0-many users.
Am I off on this? Am I doing this right? When I go to create a report or how do I make sure that the PROFILE_REPORT_LINK table is updated for every existing PROFILE and the same with when I add a PROFILE?
Thanks again...|||I think your design looks good. A query like this:
select Report_Access.*
from Report_Access
inner join Profile_Reports....
inner join Profiles...
inner join Users...
where Profile_Reports.Report_ID = @.Active_Report_ID
and Users.UID = @.Active_User_UID
easily gets the info you need.
You could make your schema simpler, but only at the cost of flexibility and ongoing user and report administration.
To update the profile_reports table, create a form that shows all the assigned profiles for a given report, and allows you to add or delete profiles. (I assume not all profiles have access to all reports?)
I doubt that you will find a way to automatically add profile_reports records whenever a new report or profile is added, unless you have pre-established report_access values by default.
blindman|||Thanks,
But what about using a trigger to update the PROFILE_REPORT_LINK table with the updated record id value in a loop adding for each opposite table id. For example: If I were to add a new PROFILE I would add a new take the new Profile_id and add a new record to PROFILE_REPORT_LINK for every existing Report_id in the REPORT table?
I am not quite sure how to do this though? What do you think?
Thanks.|||Sure, but what would the defaults be for the report _access table values?
You are setting up a security system here, so I would advise against automating new report or profile set up on principle unless you have rigidly defined business rules.
If all your profiles have access to all the reports with the same Access_Selections, then you could go with a simlper design.
blindman|||Blindman,
All USERS have access to all of the REPORTS but all with different SELECTIONS. The SELECTIONS are different depending on the Profile_ID. The Adminstrator will have access to add a new PROFILE with new SELECTIONS. The Reports will only be added by the developer in the future. There are three records in REPORTS now, but this will surely be added upon in the future.
Maybe I should force the user to define a SELECTION for each existing REPORT when the user creates a new profile from within my Front End code.
Do you think that this is a better option?
Thanks.|||Sorry, by SELECTIONS I meant to say REPORT_ACCESS|||rdj - it looks like profiles has 1-to-0/many with users table.
I think having a set of sps to handle the entire structure is the way to go:
usp_AddNewProfile
usp_AddNewUser
usp_AddNewReport
...
etc.|||Here is what I am doing now and it seems to work....
CREATE TRIGGER updated_profile on Profiles
FOR INSERT
AS
DECLARE @.Temp_Profile_Id as int
DECLARE @.Temp_Report_Id as int
DECLARE C1 CURSOR FOR
SELECT Report_Id FROM Reports
SET @.Temp_Profile_Id = (Select Profile_Id from Inserted)
OPEN C1
FETCH NEXT FROM C1
INTO @.Temp_Report_Id
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO Profile_Report_Link(Profile_Id, Report_Id)
VALUES(@.Temp_Profile_Id, @.Temp_Report_Id)
FETCH NEXT FROM C1
INTO @.Temp_Report_Id
END
CLOSE C1
DEALLOCATE C1]
Now I just have to create Triggers for when I add a Report.|||I like the SPs better than the triggers, again just on the principle of not automating a lot of security routines, but if it works for you then go for it.
blindman|||Thanks for all of the help.
I am a newbie and I need to know if I am creating this database properly.
Here is the situation. I have three main components to the database: A user, a report and a profile. Basically it is a security database deciding the access rights to the report defined by the updatable profile.
Here is how I have created the database:
USERS
----
UID [FK]
PWD
First_Name
Last_Name
Profile_Id
PROFILES
----
Profile_Id [PK]
Profile_Desc
REPORTS
----
Report_Id
Report_Desc
PROFILE_REPORTS_LINK
----------
Index [PK]
Profile_Id [PK]
Report_Id [PK]
REPORT_ACCESS
-------
Index
Access_Selections
If I start off with a UID, my app. would need to retrieve the access_selections based upon the current report selected and the profile_id previously defined by which user is logged in. I hope this makes sense... I would like to know if there is a better way of doing this and if I am to choose this way, how do I update the PROFILE_REPORTS_LINK table every time I add a new profile or a new report?
Thanks in advance.
MORI0043Is it 1-to-1 relationship between USERS and PROFILES? Is UID supposed to be a PK, not FK, and Profile_Id in USERS to be FK? And in PROFILE_REPORTS_LINK Index to stay PK while all other the fields listed are supposed to be FK, not PK, right? And REPORT_ACCESS.Index to be FK? Lastly, can a profile belong to more than one user, which means it's not 1-to-1, but rather 0/1-to-many relationship between PROFILES and USERS? If the answer is YES to all the above but the first one, - then I have one more questions, - what is the purpose of PROFILES table? Unless some additional info is present, - you can easily remove PROFILES and put UID in place of Profile_Id into PROFILE_REPORTS_LINK. Of course, this is only if I am not completely off target :)|||-Sorry... Mistype. UID is a PK.
-Yes it is a 1 to 1 relationship between USERS and PROFILES
-Sorry again.. I was going too fast. Yes in the PROFILE_LINK table both IDs are [FK]s
-You can have a profile for 0-many users... Basically it is a group profile where there would be administrators group and general group and so forth
-I don't think that you remove PROFILES because this table defines the profile which can exist for 0-many users.
Am I off on this? Am I doing this right? When I go to create a report or how do I make sure that the PROFILE_REPORT_LINK table is updated for every existing PROFILE and the same with when I add a PROFILE?
Thanks again...|||I think your design looks good. A query like this:
select Report_Access.*
from Report_Access
inner join Profile_Reports....
inner join Profiles...
inner join Users...
where Profile_Reports.Report_ID = @.Active_Report_ID
and Users.UID = @.Active_User_UID
easily gets the info you need.
You could make your schema simpler, but only at the cost of flexibility and ongoing user and report administration.
To update the profile_reports table, create a form that shows all the assigned profiles for a given report, and allows you to add or delete profiles. (I assume not all profiles have access to all reports?)
I doubt that you will find a way to automatically add profile_reports records whenever a new report or profile is added, unless you have pre-established report_access values by default.
blindman|||Thanks,
But what about using a trigger to update the PROFILE_REPORT_LINK table with the updated record id value in a loop adding for each opposite table id. For example: If I were to add a new PROFILE I would add a new take the new Profile_id and add a new record to PROFILE_REPORT_LINK for every existing Report_id in the REPORT table?
I am not quite sure how to do this though? What do you think?
Thanks.|||Sure, but what would the defaults be for the report _access table values?
You are setting up a security system here, so I would advise against automating new report or profile set up on principle unless you have rigidly defined business rules.
If all your profiles have access to all the reports with the same Access_Selections, then you could go with a simlper design.
blindman|||Blindman,
All USERS have access to all of the REPORTS but all with different SELECTIONS. The SELECTIONS are different depending on the Profile_ID. The Adminstrator will have access to add a new PROFILE with new SELECTIONS. The Reports will only be added by the developer in the future. There are three records in REPORTS now, but this will surely be added upon in the future.
Maybe I should force the user to define a SELECTION for each existing REPORT when the user creates a new profile from within my Front End code.
Do you think that this is a better option?
Thanks.|||Sorry, by SELECTIONS I meant to say REPORT_ACCESS|||rdj - it looks like profiles has 1-to-0/many with users table.
I think having a set of sps to handle the entire structure is the way to go:
usp_AddNewProfile
usp_AddNewUser
usp_AddNewReport
...
etc.|||Here is what I am doing now and it seems to work....
CREATE TRIGGER updated_profile on Profiles
FOR INSERT
AS
DECLARE @.Temp_Profile_Id as int
DECLARE @.Temp_Report_Id as int
DECLARE C1 CURSOR FOR
SELECT Report_Id FROM Reports
SET @.Temp_Profile_Id = (Select Profile_Id from Inserted)
OPEN C1
FETCH NEXT FROM C1
INTO @.Temp_Report_Id
WHILE @.@.FETCH_STATUS = 0
BEGIN
INSERT INTO Profile_Report_Link(Profile_Id, Report_Id)
VALUES(@.Temp_Profile_Id, @.Temp_Report_Id)
FETCH NEXT FROM C1
INTO @.Temp_Report_Id
END
CLOSE C1
DEALLOCATE C1]
Now I just have to create Triggers for when I add a Report.|||I like the SPs better than the triggers, again just on the principle of not automating a lot of security routines, but if it works for you then go for it.
blindman|||Thanks for all of the help.
Subscribe to:
Posts (Atom)