3

As sort of a follow up to this previous question: Normalizing a variety of properties with similar data types

I've created this setup now: http://schemabank.com/p/VwWHn

My question is am I on the right path with this method? Is there an obvious mistake to the setup I'm using, or something about the concepts of normalization I'm missing? I'm trying to go for a practical approach that works in real world scenarios, so if there is a better method to configure this database I'd be happy to hear it.

Community
  • 1
  • 1
Jane Panda
  • 1,591
  • 4
  • 23
  • 51

2 Answers2

1

How about adding the following tables:

  1. Country
  2. RatingsByCountry
  3. Ratings
  4. GameRatingsByCountry

That way, if you add a country or a rating type, you won't have to change table structures.

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • Very valid point, I sort of wedged the ratings in at the end of making that schema as a last thought. I'll have to implement that! – Jane Panda Nov 30 '10 at 22:35
1

You might want to have a platforms_data table, which would allow you to associate a game across more than one platform. This might be a conscious choice to not have this, but I thought I'd mention it.

The other thing would be that for release dates, I assume you have a separate release_dates table so that you can select all by release date. If that is not part of your plan, then you may not need the extra table and could just include it in the game model.

Another thing to consider is that games are often released in different countries on different dates. Since you are already bringing multiple countries into the schema for rating purposes, perhaps you could consider adding a country table and removing your country specific ratings tables. Then you could have a single ratings table, with each rating having a country as a foreign key. Then you would just have to create a ratings_data table to associate games with the various ratings. You could also use add the country_id to the release_date_data table. This approach will allow you to expand and add more countries without having to change your schema every time.

Wade Tandy
  • 4,026
  • 3
  • 23
  • 31
  • Platforms are a touchy subject; I'm not sure how to handle games that are released on multiple platforms because often they are released at different dates and have different properties. The release dates was conscious as well in that we -might- add more countries, though I think the big four sections (au,uk,jp,us) are the only ones anyone actually monitors. Good point about the ratings table, I don't know what I was thinking there exactly. – Jane Panda Nov 30 '10 at 22:34
  • Yeah, I figured the release dates table was probably intentional, but wanted to mention it. Since your plan includes this table though, I would definitely associate countries to the release dates (in the release_dates_data table, so you'd have three foreign keys). – Wade Tandy Nov 30 '10 at 22:37