0

I'm fairly new to SQL. I just wanted to create a database from scratch to learn.

So I have a table called Addresses and I want to create two tables dependent on it that are Country and State, Im not sure what script to use for that.

Thanks in advance

1

I am not sure that I would consider this to be an issue of inheritance, and from the description, I would say that the Address table would depend on the other two tables instead of those two tables depending on the Address table.

But that minutia aside, I would recommend:

  1. If you are only storing U.S. or maybe U.S. and Canada addresses, then you can do a "StateRegion" table. But in that case you don't really need a Country table.

  2. If you are storing addresses from many countries, then you would want a Country table, but the StateRegion table is trickier as that varies greatly between countries. I will have to look to see if there is a standardized list for that.

  3. For countries, a Country table should be just the ISO list of countries. There is really no need for a separate ID field as the ISO country codes are an acceptable standard (hence the "S" in ISO :-). Just make CountryCode (I prefer the 2-character codes over the 3-character codes) a CHAR(2) NOT NULL.

  4. If storing non-US/Canada-only addresses:

So, generally speaking:

CREATE TABLE County
(
   CountryCode CHAR(2) NOT NULL PRIMARY KEY,
   CountryName VARCHAR(100) NOT NULL
);

CREATE TABLE Address
(
   AddressID INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
   UserID INT NOT NULL, -- FK to some User table
   AddressLine1 NVARCHAR(100) NOT NULL,
   AddressLine2 NVARCHAR(100) NULL,
   City NVARCHAR(100) NULL,
   StateProvince NVARCHAR(100) NOT NULL, -- or INT FK to StateProvince table?
   PostalCode NVARCHAR(20) NOT NULL,
   CountryCode CHAR(2) NOT NULL -- FK to Country table
)
0

You would be more likely to have the Address table have foreign key dependencies on the Country and State tables than the other way around. Something like the below shows you one way to create the foreign key reference.

Of course, the fields required for an address change depending on which country you are in; you may need to research the different requirements and adjust the fields accordingly.

CREATE TABLE Country
(
CountryID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, CountryName VARCHAR(500) NOT NULL
, CountryCode VARCHAR(10) NOT NULL
)

CREATE TABLE Addresses
(
AddressID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
, AddressLine1 VARCHAR(1000) NOT NULL
, AddressLine2 VARCHAR(1000) NULL
, City VARCHAR(20) NOT NULL
, [State] CHAR(2) NOT NULL
, Country INT NOT NULL REFERENCES Country(CountryID)
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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