w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
How to avoid arrays in relational database?

@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.

© Copyright 2018 w3hello.com Publishing Limited. All rights reserved.