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