0

I have a website I'm building for a friend's business and I am wondering how to go about storing the data. So far I have designed this diagram. Let me give an example:

Say there are 2 events in a karate tournament: Sparring and Forms. Each event can have it's own division: Sparring 4-6 year olds, Sparring 8-10 year olds, etc... And each student can sign up for either 1 or all events.

My question is, does the image below suffice for what I just explained in the example, minus the cardinality.

My second questions is, what is the actual database going to look like? Right now, I can think of the following tables to add:

  • students
  • events
  • divisions
  • student_divisions (student_id, division_id) Is this correct? Because I need to be able to store multiple divisions to one student

My ER Diagram

Thanks, any pointers to help me be a better designer would be helpful.

johnslay
  • 681
  • 1
  • 6
  • 17

1 Answers1

2

Keep it simple, make it fun.

  • lose the "id" term (make it student_id or division_id or event_id) Use terms that make it really clear what is being identified. Same for "name".. is that student_name or event_name?
  • Put as much detail as possible on each student (student_id, current_belt_ranking, date_of_birth (--> age), student_name.
  • Events (event_name, division_id, date, location) (I'd make key = event_name/division pair) or in the alternative "Sparing_8-10", "Forms_4-6"
  • Divisions (division id, other stuff as indicated)
  • student/events table (student_id matched with event_name/division_id pair)

Analyse for first, second, third normal form.

  • First Normal Form (1NF): In plain English, no row of data can have repeating elements. All occurances of a record type must contain the same number of fields. ex( you would NOT put the events a given student was signed up for within the student table. Put that stuff in a seperate table.)

  • Second Normal Form (2NF): In plain words, are there any data elements in a single row that are only dependent on a portion of the concatenated primary key? If so, remove those elements into an additional table. (ex: you wouldn't have student name, age, and date_of_birth within the student/events table... )

  • Third Normal Form (3NF): Every non-prime attribute of your tables is non-transitively dependent on every superkey of tables. 3NF is violated when a non-key field is a fact about another non-key field.(Yeah, like does that even make sense? And frankly I can't provide an example of this for you with your example...Let me do a bit of research... With your system, I don't think there are any tables with large numbers of fields to even get close to this violation. Remember 3NF deals with non-key fields relating to other non-key fields. )

Give it a shot, then build your queries and see if they make sense?

zipzit
  • 3,778
  • 4
  • 35
  • 63