Possible outline structure for this data is required

[Resorte]

Hello People:

I am developing an ASP.NET intranet and website for a secondary school with five years of students. The site must store and use data from a database. Most of the text for the pages must come from the database, (I am doing in MsAccess)

The site must be a secure site with three types of users: students, teachers and administrators. All users must be able to change his/her own passwords and have individual profiles (stored in the database) that can be edited. The profiles are different for the different types of user (eg teachers have rooms, but students do not) and some parts of the profile cannot be edited by the user (eg a student cannot edit their name, although they may be allowed to edit their preferred name)

The following simplifying assumptions should be made

• Each year has a teacher who is the Year Leader
• Each subject (eg English, History) has a teacher who is the Subject Coordinator
• A teacher can be the subject coordinator of only one subject
• A teacher can be the year leader of just one year, and there are 5 year leaders, one for each year
• A teacher cannot be both a subject coordinator and a year leader at the same time.
• Each student is enrolled on just one year at a time (eg is a second year student in 2011/12)
• Each year each student studies 8 subjects

CAN ANYBODY CAN HELP ME WITH THE ACCESS DATABASE STRUCTURE PLEASE. I LEFT MY TABLES COMPLETED IN THE ZIP FILE BELOW. anY HELP REALLY APPRECIATED

THANKS

resortes

[help_me_with_access]

just one comment....I would help but this project is too large to offer free advice. IMO, it isn't possible. Just wanted to offer my opinion.

Also, if you're working with .NET 3.0 or higher (or even 2.x for that matter) you shouldn't need much help with simple database structuring. If you use SQL Server too, a lot of issues you may run into could probably go away or at least be automated in some way.

[Resorte]

Hi

Thanks for your comment. however developing in ASP.NET is not the problem. My issue is with the database because relationship must be ok before i can integrated. please check the attach file so it will be more clear. If anybody has an idea please help

Thanks

Resorte

[help_me_with_access]

the tables look OK to me. but this is a very *unique* situation. what were you worried about, again? I forget...

are you worried about the restrictions and how they are relevant to the relationships? If that is what you're asking, I'm going to advise that you cover the restriction scenarios with coding. That's the way I usually do it.

I'm not even sure if that type of thing can be completely accounted for by table relationships. I'm doubting it. There's only so much you can do with data structure. Proper structure is never perfect because database architecture is always built for a business driven by humans. and of course humans always refuse to live their life that's consistent and works well with database normalization.

you might just have to deal with it using ADO or ASP coding.

the only thing I would point out is that I'm not sure if the "years" table should be there. that's the only thing that looks odd to me, but if per-annum is an important part of your paper trail needs, then it obviously *should* be a table in the mix somewhere.