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));

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...