Theory Question--Two tables relating in more than one way?
Date: 28 Feb 2005 19:07:15 -0800
Message-ID: <1109646435.021895.219340_at_l41g2000cwc.googlegroups.com>
All,
I am posting the create scripts, which were created for the sole purpose of asking this theoretical question.
The basic question is this. Is it theortically acceptable for two tables to relate to each other in more than one way?
The example I am posting has to do with cars and owners. Every owner has at least one primary car, and may have one or many others.
I can think of two ways to handle this, and I see pluses and minuses to both.
The first method uses a non-nullable field to ensure that every owner has a primary car. However, some business logic would be required when an owner sells his primary car and wants to designate one of their other cars as primary. Also, when adding a new car, some logic would be required to move the old primary car into the owner/car relationship table (there are probably other cases where this would be problematic as well).
The second method uses only a single relationship between the two tables, and adds a flag on the relationship table that designates one row as the "primary" row. This allows more uniform (i.e. set-based) logic, but doesn't have the advantage of having the database easiliy enforce that the owner has at *least* a primary car through a non-nullable field.
Does the first method violate third normal form? Is one way better than the other? Is there a another, superior way this could be handled?
I hope I've made my questions clear. Thanks in advance for any insight.
Method 1:
CREATE TABLE [dbo].[car] (
[car_id] [int] IDENTITY (1, 1) NOT NULL ,
[car_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[owner] (
[owner_id] [int] IDENTITY (1, 1) NOT NULL ,
[owner_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[primary_car_id] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[owner_x_car] (
[owner_x_car_id] [int] IDENTITY (1, 1) NOT NULL ,
[car_id] [int] NOT NULL ,
[owner_id] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[car] ADD
CONSTRAINT [PK_car] PRIMARY KEY CLUSTERED ( [car_id] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[owner] ADD
CONSTRAINT [PK_owner] PRIMARY KEY CLUSTERED ( [owner_id] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[owner_x_car] ADD
CONSTRAINT [PK_owner_x_car] PRIMARY KEY CLUSTERED ( [owner_x_car_id] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[owner] ADD
CONSTRAINT [FK_owner_car] FOREIGN KEY ( [primary_car_id] ) REFERENCES [dbo].[car] ( [car_id] )
GO
ALTER TABLE [dbo].[owner_x_car] ADD
CONSTRAINT [FK_owner_x_car_car] FOREIGN KEY ( [car_id] ) REFERENCES [dbo].[car] ( [car_id] ), CONSTRAINT [FK_owner_x_car_owner] FOREIGN KEY ( [owner_id] ) REFERENCES [dbo].[owner] ( [owner_id] )
GO
Method 2:
CREATE TABLE [dbo].[owner] (
[owner_id] [int] IDENTITY (1, 1) NOT NULL ,
[owner_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[car] (
[car_id] [int] IDENTITY (1, 1) NOT NULL ,
[car_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[owner_x_car] (
[owner_x_car_id] [int] IDENTITY (1, 1) NOT NULL ,
[car_id] [int] NOT NULL ,
[owner_id] [int] NOT NULL ,
[isprimary] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[owner] ADD
CONSTRAINT [PK_owner] PRIMARY KEY CLUSTERED ( [owner_id] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[car] ADD
CONSTRAINT [PK_car] PRIMARY KEY CLUSTERED ( [car_id] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[owner_x_car] ADD
CONSTRAINT [PK_owner_x_car] PRIMARY KEY CLUSTERED ( [owner_x_car_id] ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[owner_x_car] ADD
CONSTRAINT [FK_owner_x_car_car] FOREIGN KEY ( [car_id] ) REFERENCES [dbo].[car] ( [car_id] ), CONSTRAINT [FK_owner_x_car_owner] FOREIGN KEY ( [owner_id] ) REFERENCES [dbo].[owner] ( [owner_id] )
GO Received on Tue Mar 01 2005 - 04:07:15 CET