CODEWITHSUNDEEP
sqlrecord
scifirocket
scifirocket

Reputation: 1051

adding multiple values to sql

say i have a column defined as Address. also, I have a record, let's call it Rudy's. now Rudy's has multiple addresses, so I need to include multiple address so that they are all searchable. what is the best way to approach a solution in SQL?

Upvotes: 0

Views: 175

Answers (2)

qbantek
qbantek

Reputation: 695

You could try something like this:

CREATE TABLE [dbo].[Person](
[PersonId] [int] IDENTITY(1,1) NOT NULL,
[FullName] [varchar](50) NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED ([PersonId] ASC))
GO

CREATE TABLE [dbo].[Addresses](
[AddressId] [int] IDENTITY(1,1) NOT NULL,
[PersonId] [int] NOT NULL,
[AddressLine1] [varchar](50) NULL,
[AddressLine2] [varchar](50) NULL,
[City] [varchar](50) NULL,
[State] [varchar](4) NULL,
[Country] [varchar](50) NULL,
CONSTRAINT [PK_Addresses] PRIMARY KEY CLUSTERED ([AddressId] ASC))
GO

ALTER TABLE [dbo].[Addresses]  
WITH CHECK ADD  CONSTRAINT [FK_Addresses_Person] FOREIGN KEY([PersonId]) 
REFERENCES [dbo].[Person] ([PersonId])
GO

ALTER TABLE [dbo].[Addresses] CHECK CONSTRAINT [FK_Addresses_Person]
GO

Of course you can have it as complex as you want and follow the previous advice for storing address types etc.

It might help to download MS examples from http://sqlserversamples.codeplex.com/ and follow their best practices.

Upvotes: 1

Novitzky
Novitzky

Reputation: 4876

You should add a child table with an Address column. You will have one to many relation where address is stored in a child table. You can add as many addresses per user as you want. Also you can add extra info like address type (home, work or primary, secondary ect.)

I wouldn't go for one column for address. If this is a postal address it is better to have more columns such as street, town, house number ect. Then you can have an advantage of using indexes on your columns.

Upvotes: 2

Related Questions