February 6, 2011 at 11:21 pm
This is out of interest/learning only, this is not live code.
Given the following tables and constraints:CREATE TABLE [dbo].[Clients]
(
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[ClientName] [nvarchar](50) NOT NULL,
[BillingAddressID] [int] NOT NULL,
CONSTRAINT [PK_Clientsss] PRIMARY KEY CLUSTERED ([ClientID] ASC)
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[BillingAddress]
(
[BillingAddressID] [int] IDENTITY(1,1) NOT NULL,
[BillingAddressLotsOfColumns] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_BillingAddress] PRIMARY KEY CLUSTERED ([BillingAddressID] ASC)
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[Client] WITH CHECK
ADD CONSTRAINT [FK_Client_BillingAddress] FOREIGN KEY([BillingAddressID])
REFERENCES [dbo].[BillingAddress] ([BillingAddressID])
GO
ALTER TABLE [dbo].[Client] CHECK CONSTRAINT [FK_Client_BillingAddress]
GO
What would be a more elegant and efficient solution to insert data into the Client table than the following code:
DECLARE @lotsOfColumnsData AS NVARCHAR(50) = N'Some lines of address data'
INSERT INTO dbo.BillingAddress (BillingAddressLotsOfColumns)
VALUES (@lotsOfColumnsData)
DECLARE @newBillingAddressID INT
SELECT
@newBillingAddressID = ba.BillingAddressID
FROM
dbo.BillingAddress AS ba
WHERE
ba.BillingAddressLotsOfColumns = @lotsOfColumnsData
INSERT INTO dbo.Client (ClientName, BillingAddressID)
VALUES (N'Bobby', @newBillingAddressID)
Thank you
February 7, 2011 at 11:04 am
It depends on where you're getting this information from and what you're planning to do with it.
For a single-record-at-a-time insert, this is a perfectly functional solution with little drawback. If you're looking for a multi-record insert for robust functionality, then the solution depends on how you're getting your data.
For instance, your code currently assumes Bobby will be the only person who is getting address updates. Certainly there are more people in the universe not named Bobby than there are those who are named Bobby, right? (Okay, for all I know there could be an entire galaxy full of "Bobby"s, but bear with me here).
This being the case, I would recommend using a JOIN table to keep ClientID and BillingID together. So your table structure would be:
CREATE TABLE [dbo].[Clients]
(
[ClientID] [int] IDENTITY(1,1) NOT NULL,
[ClientName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Clientsss] PRIMARY KEY CLUSTERED ([ClientID] ASC)
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[BillingAddress]
(
[BillingAddressID] [int] IDENTITY(1,1) NOT NULL,
[BillingAddressLotsOfColumns] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_BillingAddress] PRIMARY KEY CLUSTERED ([BillingAddressID] ASC)
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[ClientBillingAddress]
([ClientID] [int] NOT NULL,
[BillingAddressID] [int] NOT NULL,
CONSTRAINT [PK_ClientIDBillingID] PRIMARY KEY CLUSTERED ([ClientID,BillingAddressID] ASC),
CONSTRAINT FK_BillingAddress] FOREIGN KEY([BillingAddressID])
REFERENCES [dbo].[BillingAddress] ([BillingAddressID]),
CONSTRAINT FK_Client] FOREIGN KEY([ClientID])
REFERENCES [dbo].[Client] ([ClientID]),
)
ON [PRIMARY];
ALTER TABLE [dbo].[ClientBillingAddress] CHECK CONSTRAINT [FK_BillingAddress]
GO
ALTER TABLE [dbo].[ClientBillingAddress] CHECK CONSTRAINT [FK_Client]
GO
You insert your data into your two tables, track the IDs and how they fit together, and store them in this JOIN table.
The great thing about this structure is you can keep historical versions of old addresses without having to update the Client record itself.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply