Mateusz Cieplak Mateusz Cieplak - 2 months ago 12
Java Question

SQLite how to build DB schema for unknown column amount (multiple tables?)

every body:)

I have litle problem with biuld schema of my database. Imagine there is Match. You know match is between two teams. There is data of match. And there is a lot of events: goals, fouls, etc. I build one table "match" there is 3 fields: team 1 (string), team 2(string), date (long). And I want to store every event. So do I have to create for every match (for example I want to create one match per day) new table "events" and bind (put name this event's table to column as string) it to fourth field or (may be better idea) create dynamically (via Java) a lot of columns for every match? Could somebody help me? Thanks for reply :)

Answer

No, lots of columns for every Match is not normalized. This should not be done.

There are many Event rows for every Match. What you need is a table for each and a one to many relationship. You'll need a foreign key in the Event table to point back to its Match parent so you can JOIN them together.

You aren't going far enough, in my view. You need Team with many Player rows. A Foul should be assessed against a Player. If you want to model the problem properly you need to do more work and thinking.

Lots of things to think about:

1 Team -> many Players
1 Player -> one Team

1 Match -> 2 Teams
1 Match -> 0 or more Goals
1 Goal -> 1 Team

1 Player -> 0 or more Goals
1 Player -> 0 or more Fouls
Comments