5 Replies - 764 Views - Last Post: 03 April 2013 - 03:01 PM Rate Topic: -----

#1 CodMnk2b  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 14
  • Joined: 07-February 13

Database Design Advice

Posted 03 April 2013 - 10:01 AM

I'm trying to design the database for my application. I only worked with tutorials of 3 or 4 tables and 1 or 2 relationships, so with more I'm a bit confused if I'm doing the right things.
Any advice will be appreciated. :smile2:/>/>/>/>/>

The database is for a dance academy that register students (their monthly subscriptions and attendance(daily)), instructors(and their payrolls), Activities (like a calendar),
indexes the documents of the MyDocuments folder. Also tracks some actions made by the user.

I thought of adding the approach of a Person table that is inherited by Students and Instructors, but not sure if that will complicate more things, by the time of making queries.

Posted Image

Large Size: http://i.imgur.com/kDitYiT.png

Model was made with Entity Framework 5.0, and will be mapped to a local database (.sdf).


Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Database Design Advice

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,147
  • Joined: 12-June 08

Re: Database Design Advice

Posted 03 April 2013 - 10:06 AM

Why not just have one person table, and add a column to it that is 'role'. 0 = student, 1 = instructor?
Was This Post Helpful? 0
  • +
  • -

#3 CodMnk2b  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 14
  • Joined: 07-February 13

Re: Database Design Advice

Posted 03 April 2013 - 11:07 AM

Thanks for the quick response, I thought of doing something like that, but that unables me to register a student that is also an instructor. My solution is not good anyway since it duplicates data.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 8379
  • View blog
  • Posts: 31,147
  • Joined: 12-June 08

Re: Database Design Advice

Posted 03 April 2013 - 11:24 AM

That wouldn't make sense.. when would a student be an instructor?

If you absolutely must have 'both' then add a new role that is just that "both".

'role'
0 = student,
1 = instructor
2 = both
Was This Post Helpful? 0
  • +
  • -

#5 CodMnk2b  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 14
  • Joined: 07-February 13

Re: Database Design Advice

Posted 03 April 2013 - 11:32 AM

It does happen in my client's academy.
Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 782
  • View blog
  • Posts: 1,663
  • Joined: 30-January 09

Re: Database Design Advice

Posted 03 April 2013 - 03:01 PM

You're better off having a bridging table, rather than defining a role of "both". Using a bridging table is extensible, and properly normalises your data:

Persons
    ID
    FirstName
    Surname
    etc.


Roles
    ID
    Role


PersonRoles
    ID
    PersonID (FK -> Persons.ID)
    RoleID (FK -> Roles.ID)


Unfortunately, I can't read the writing in the image you posted, but if a person can be an instructor.student in a particular course, then you might want to add CourseID to the PersonRoles table.
Was This Post Helpful? 2
  • +
  • -

Page 1 of 1