Relational Desing Question

I am trying to desing a set of tables but I have come up against a
normalization problem and could use a spot of help.
Here is what I want to acheive -
TableDevices
--
DeviceId (PK), DeviceType
TableDeviceTypeADetails
---
DeviceId (PK), details columns.....
TableDeviceTypeBDetails
---
DeviceId (PK), details columns......
Basically, I want to create a master table to hold a reference to all
devices - of which can be of different types. The primary key is the
DeviceId. Each Device has a unique setup structure thus I want to
create a set of config tables for each device type and insert the
device details in the corresponding tables for that type yet uniquely
identified by the DeviceId in the master table.
The problem is that I get a one to one reltaionship between the master
table and each of the device setup tables.
This has to be a common problem and I cannot seem to find the correct
design pattern for this situation.
Thanks.On 21 Apr 2006 14:26:41 -0700, dubian wrote:
(snip)
>This has to be a common problem and I cannot seem to find the correct
>design pattern for this situation.
Hi Dubian,
There are two possible answers. (Actually, there are more - but these
are the two I'd recommend).
The fist is the simplest. (Note: in the DDL, I have to make assumptions
for datatypes - you'll have to change them to the correct types).
CREATE TABLE Devices
(DeviceId int NOT NULL,
DeviceType char(1) NOT NULL,
-- Other columns,
PRIMARY KEY (DeviceId),
CHECK (DeviceType IN ('A', 'B', 'C')
)
--
CREATE TABLE DeviceTypeADetails
(DeviceId int NOT NULL,
-- Other columns,
PRIMARY KEY (DeviceId),
FOREIGN KEY (DeviceId)
REFERENCES Devices (DeviceId)
)
and the same idea for the other device types.
And here's the second one. It's a little more comlpicated, but allso
more robust since it checks that you don't add type B's details for a
type A device (you can do that in the first version).
CREATE TABLE Devices
(DeviceId int NOT NULL,
DeviceType char(1) NOT NULL,
-- Other columns,
PRIMARY KEY (DeviceId),
UNIQUE (DeviceId, DeviceType),
CHECK (DeviceType IN ('A', 'B', 'C')
)
--
CREATE TABLE DeviceTypeADetails
(DeviceId int NOT NULL,
DeviceType char(1) NOT NULL DEFAULT 'A',
-- Other columns,
PRIMARY KEY (DeviceId),
CHECK (DeviceType = 'A')
FOREIGN KEY (DeviceId, DeviceType)
REFERENCES Devices (DeviceId, DeviceType)
)
Hugo Kornelis, SQL Server MVP|||"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:5hli4292305ota0sujb7b9233g6r6bd82m@.
4ax.com...

> And here's the second one. It's a little more comlpicated, but allso
> more robust since it checks that you don't add type B's details for a
> type A device (you can do that in the first version).
> CREATE TABLE Devices
> (DeviceId int NOT NULL,
> DeviceType char(1) NOT NULL,
> -- Other columns,
> PRIMARY KEY (DeviceId),
> UNIQUE (DeviceId, DeviceType),
> CHECK (DeviceType IN ('A', 'B', 'C')
> )
> --
> CREATE TABLE DeviceTypeADetails
> (DeviceId int NOT NULL,
> DeviceType char(1) NOT NULL DEFAULT 'A',
> -- Other columns,
> PRIMARY KEY (DeviceId),
> CHECK (DeviceType = 'A')
> FOREIGN KEY (DeviceId, DeviceType)
> REFERENCES Devices (DeviceId, DeviceType)
> )
Hugo,
Although this design is very familiar I've been unable to find a reference
for it in any textbook. Most examples (Pascal's and Date's for example)
don't mention any constraint at all or they just imply that the DBMS
enforces the rule of mutual exclusivity. Do you know of a published source
for the technique of using a compound foreign key and a unique constraint?
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
--|||On Fri, 21 Apr 2006 23:26:44 +0100, David Portas wrote:
(snip)
> Do you know of a published source
>for the technique of using a compound foreign key and a unique constraint?
Hi David,
No. Sorry.
I've picked this up from one of Joe Celko's posts in the usenet groups
and memorized it in my list of usefull techniques. :-)
Hugo Kornelis, SQL Server MVP|||David Portas wrote:
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:5hli4292305ota0sujb7b9233g6r6bd82m@.
4ax.com...
>
> Hugo,
> Although this design is very familiar I've been unable to find a reference
> for it in any textbook. Most examples (Pascal's and Date's for example)
> don't mention any constraint at all or they just imply that the DBMS
> enforces the rule of mutual exclusivity. Do you know of a published source
> for the technique of using a compound foreign key and a unique constraint?
> --
> David Portas, SQL Server MVP
David,
I am not sure what you are looking for specifically, but just in case
you don't know: a foreign key must reference a unique (compound) key,
either with a UNIQUE constraint or PRIMARY KEY constraint. SQL-Server's
implementation will probably allow any uniquely indexed column(s).
The mutual exclusivity can be proven by the fact that the single column
(in this case DeviceId) is unique by definition, which guarantees that
there can be only one DeviceType value for that DeviceId.
Therefore, the referenced table does not need a check constraint. But to
get this to work, the referencing table does need a check constraint
with only one allowed value (and should not allow NULL).
There have occasions where I have advised this technique as far back as
2001 (maybe earlier).
HTH,
Gert-Jan|||Thanks Hugo - I'll give it a go.