How to avoid arrays in relational database?

Question

My database describes companies websites. A company website has several attribute: URL, load time, etc. Each website is targeting one or several countries. I have problems expressing the relation between the website and the countries.

My setup number 1 looks like this:

• A Company table
• A Country table
• A Website table, with fields WebsiteId, CompanyId (refers to Company.id), Countries (array referring to Country.id) and several attributes like URL, LoadTime, etc.

My setup number 2 looks like this:

• A Company table
• A Country table
• A Website table, with fields WebsiteId, CompanyId (refers to Company.id) and several attributes like URL, LoadTime, etc.
• A WebsiteCountries table, with fields WebsiteId, CountryId

I have problem with both approaches:

• Setup number 1 uses an array of Country.id, which makes it hard to maintain
• In setup number 1, CompanyId has a unique constraint in the Website table, but allows duplicate in setup number 2. More generally, CompanyId + Countries could act as a candidate key in setup 1. This disturbs me a bit : am I wrong and is it the best approach?

Answer 1

@Jodrell already gave you the correct answer - "setup number 2" with compound PK is the best solution. I'll just explain it in details.

There are companies, countries and websites. For each of them we create a separate table.

Now it's time for references:

• If a company may have only one website, then it's one-to-one relationship. If it may have many websites, then it's one-to-many relationship. In both cases you have a foreign key CompanyId in Website table - it'll only differ in UNIQUE constraint.

• You write "Each website is targeting one or several countries." Additionally, for every country there may be one or more websites (for different companies). It means that it's a classical many-to-many relationship. In such case you must add a junction table - WebsiteCountries. Here you'll have a compound primary key that constists of two columns: (WebsiteId, CountryId). This'll prevent from duplicates.

Hope this helps.