I have information about a Shop. It belongs, to a City. City belongs to the Country. And Country belongs to a Continent.
Should I make one big table that contains 4 columns with information about every Shop. Or should I make 4 tables. First with Continent column. Second with Country and it’s relation to a Continent. Third with a City and it’s relation to a Country. Finally fourth with a Shop and it’s relation to a City.
What is the best approach?
Advertisement
Answer
So, the usual approach to this would be to use separate tables linked with foreign keys.
Why? Well lets say you open 10 new shops. Sure you could just add their details to your shop table but the more shops you open and the harder placenames are to spell, the more likely it is that errors will creep in. Having a linked table means you have a single version of the truth. In other words you have Mississippi once, and no risk of also having Misissippi or Missisippi.
Not only that but in the one table approach you are storing country, city, continent using text. So if your shop inventory or sales get big and you want to generate a report by city or country, that query is going to get slow really quickly.
By having a seperate table with an integer primary key, SQL can search really fast for sales in that city, or country. Not only that but it’s easier to create reports such as all sales by shop for City x or Country x.