Database design help- asp.net-mvc

Question

0.00/5 (No votes)

See more:

Alright so I need help designing a database that mimics an excel spreadsheet. I want it switched to a web application because once a database gets bigger than 25/50 users... its hard to maintain an excel sheet. Attached is an image of the excel sheet.

Please read the colored captions for each section! Its important!

Matrix_Picture[^]

This is what my current database diagram looks like...

Database_Diagram[^]

What I need to do & Information:
1)I want to be able to share 1 application users table between multiple solutions... This is not a necessity immediately... but i will need it in the future.
2) The matrix is used to keep track of a students progress in achieving certification.
3) A User registers on a web application. An admin assigns That user is assigned to a PRIMARY AND SECONDARY CONCENTRATION
4) Based on the concentration the user is assigned he gets links to certain exercises. If you look at the picture, User 1 is concentration 5 (tiki tiki experts, primary concentration) & 6 (Tiki Facilitators, secondary concentration). The user gets links to modules, training, and reading based on these concentration... User 1, gets assigned module 5.00, and 5.01.
5) The user is given a score on performance (this score is manually entered by an instructor).
6) I also need a way to keep a %complete for each user who uses this matrix. The %complete would be calculated based on an algorithm of the scores they recieved on modules, training, and reading.
7) IMPORTANT: The modules, training, and reading, need to have 'weights'... meaning the weight of each of these 3 categories would be 33%, but I want to be able to adjust these weights in the future (maybe change the modules to 50%, and training and reading to 25% each).
7b) Having weights in modules, training, and reading is important because weights are used to calculate the %complete for each user.

I am designing this in Visual Studios, using entity framework, and the MVC design pattern.

Please advise on what I can do.
-I am super confused about how I can assign many modules, training, and reading, to many concentrations.
-I am confused on how to keep track of scores for each module for each user.
-I am confused on how I can assign a primary and secondary concentration to a user... and making sure they don't have access to modules, training, and reading that they are not assigned to.

What I have tried:

Please look at database diagram, I have scaffolded controllers and views for most of those tables.

Posted 1-Mar-16 16:34pm

Member 12362975

Updated 1-Mar-16 20:33pm

PIEBALDconsult

Add a Solution

Comments

Mehdi Gholam 2-Mar-16 0:19am

You can't expect any one here to build your application for you with such a broad question, try to ask specific targeted questions for more meaningful answers.

Member 12362975 2-Mar-16 9:29am

Im not expecting any one to build my application. I need help getting the relationships between table working properly. Thanks for the useful reply though.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Answer 1 · 2016-03-01T20:33:00

You design the database step by step. You describe your problem. Now read what you wrote and create tables:
[Users]
[Certifications]
[ConcentrationTypes] -> this contains values [Modules][Training][Reading]
[Concentration] -> this contains link (foreign key) to Certification, weight and type
[UserConcentration] -> this contains three important fields, id, user_id and concentration_id -> this is how you assign multiple users to multiple concentrations - google "many-to-many relationship in the database" -> other then that, you can put scores for each concentration here. Or weight if you want same concentration to be of different weight depending on the user or certificate

In general, you read the description of your domain and assign to each noun separate table. Or if you can generalise (as in modules, training, reading) where you have same fields and/or functionality you create one table for all of them and another table that describes the type of the row item.

If this helps, please accept the solution. Thank you and good luck.