0

I'm making a program for an A level project which has users and each user can have many scores. This is the first time I'm making a database (SQL). So far I've got to this stage:

  • user(ID[PK], Username, Hashed_PW, Theme_Colour)

  • gameMode1(ID[FK], Date_Time, Score)
    The primary key is a composite of ID/Date_Time.

  • gameMode2(ID[FK], Date_Time, Score)
    The primary key is a composite of ID/Date_Time.

One user record can have many gamemode1 records. One user record can also have many gameMode2 records.

Gamemode1 and gamemode 2 have no relation.

What would be better:

  • Have separate tables for gamemode1 and 2 OR

  • Have the same table for gamemode 1 and 2 and introduce a type field
    (Would this cause unnecessary data redundancy?)

Also what will help make this in 3NF? (My teacher is not sure so I asked here.)

philipxy
  • 14,867
  • 6
  • 39
  • 83
J.Johnson
  • 89
  • 1
  • 1
  • 8
  • In general, if two tables are going to have exactly the same columns, there is a good argument for putting them in the same table. This is not always true, but it is a place to start. – Gordon Linoff Nov 19 '17 at 14:34
  • How @GordonLinoff said is not necessary have two tables with same columns. But then you make this, think about extension. If you feel your gamemode1 can have more additional columns in future, it will not same as gamemode2 which can have different columns. All depends on criteria, task, and what you are expect. – daremachine Nov 19 '17 at 14:45
  • 1
    Thank you for your help. I feel like although their scores will both be floats they are very different and in the future will have very different fields so probably best to have three tables in total – J.Johnson Nov 19 '17 at 15:49
  • 1
    Your tables look like they're already in 3NF. For a formal analysis, read up on [functional dependencies](https://stackoverflow.com/questions/4199444/functional-dependency-and-normalization) and list them, then look for any attributes that aren't dependent on the whole primary key. If any attribute depends on a part of the key or on a non-prime attribute, you don't have 3NF. – reaanb Nov 19 '17 at 16:58
  • There's no such thing as "better"/"best" in engineering unless you define it. Also unfortunately all reasonable practical definitions require a ridiculous amount of experience with a ridiculous number of factors that interact with chaotic sensitivity to details. Make straightforward designs. When you demonstrate via measurement that a design and all alternatives you can think of have problems (whatever that means at the time), then ask a very specific question. Which should also define "better"/"best". Re "3NF" read a textbook & again, when you know what it means, ask a specific question. – philipxy Nov 25 '17 at 09:36

0 Answers0