Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Hi. I created this database to just to sharpen my skill. I have one Question - when I create I make the FOREIGN KEY personID in Addresses table but I changed it to be AddressID in Persons table because I can have a list of Addresses. When I create program is that fine? I create check constraint to check the canadian postal code is my constraint right ?

CREATE TABLE dbo.Addresses(
  AddressID int IDENTITY(1,1) PRIMARY KEY,
  Street varchar(255),
  City varchar(255),
  Province varChar(255),
  PostalCode varchar(10) CHECK(PostalCode>='t1w1v1' and PostalCode<='t9w9v9')  
)

CREATE TABLE dbo.Persons (
  PersonID int IDENTITY(1,1) PRIMARY KEY,
  FirstName varchar(255) NOT NULL,
  LastName varchar(255) NOT NULL,
  MiddleName varchar(255) null,
  DateOfBirth date NOT NULL,
  Gender char(1) NULL CHECK(Gender='M' OR Gender='F'),
  AddressID int FOREIGN KEY REFERENCES dbo.Addresses(AddressID)
)
share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

Yes, that would be fine if your design goal is that Persons have only 1 Addresses. With this design, each Persons can have 1 Addresses but two or more Persons can have the same Addresses.

It's all a matter of your business needs. If the above is what you are trying to get, then yes, it is correct.

However, I think it's most common the other way around where the Addresses would have a foreign key to the Persons because a Persons could have more than one Addresses.

As for your constraint to check the postal code - well first off you are missing the space and it's lower case. Whether that will work will depend on which database system you are using. I tested it with PostgreSQL and it does not work.

I don't think you can really have such a simple constraint to fully validate a Canadian postal code. For example, there are some letters and some numbers which are never used. I'm a bit rusty on my Canadian postal office codes but I seem to recall that number 5 is never used as it's too similar to S, etc.

share|improve this answer
2  
The best way to confirm postal codes is to compare the user input to a known-good list of valid codes. There are many sources on the Internet for Canadian postal codes that are update periodically and are not terribly cost prohibitive. The advantage here would be you can fill out the city and province automatically for the user (or at least validate them). –  Max Vernon Feb 8 at 15:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.