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?
Add a comment
|
2 Answers
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.
1 Comment
Novitzky
@scifirocket - Read about relational databases and about database normalization: en.wikipedia.org/wiki/Database_normalization. You can find some nice examples on youtube as well.
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.
