CODEWITHSUNDEEP
sqldatabasedatabase-schema
Slavisa Perisic
Slavisa Perisic

Reputation: 1110

Database organize dilemma (simple web app)

I have an idea to build an application where users can register and keep some kinds of scores among each other.

For example users A, B and C all have their special score:

A-B for example 11-9
B-C for example 22-80
A-C for example 15-15

I have an idea but I'm not sure it's the right way, so I'd like to hear some of your opinions.

Thank you

Upvotes: 0

Views: 44

Answers (3)

user359040
user359040

Reputation:

For multi-player matches:

Table User
----------
User_ID (Primary Key)
User_Name

Table Match
-----------
Match_ID (Primary Key)
Match_Date

Table User_Match
----------------
Match_ID (Primary Key)
User_ID  (Primary Key)
Score

Upvotes: 1

Kent
Kent

Reputation: 195229

user Table
-----------
id | name
-----------
1  | a
2  | b
3  | c




score table

---------------
userId1|userId2|user1score|user2score
1|2|11|9
2|3|22|80
1|3|15|15

Upvotes: 1

zmilojko
zmilojko

Reputation: 2135

table User:

id  name
1    A
2    B
3    C

table MutualScore

user_id_1   user_id_2  score_1   score_2
1           2          11        9
...

Set indexes correcty, so both user_id_1 and 2 are foreign keys towards table User and make the pair user_id_1 and 2 unique. However, as far as I know, making sure that there is no reverse pair breking the uniqueness, you need to make an insert trigger, and you will have a clear structure.

Upvotes: 2

Related Questions