I chose llblgen forums to post this cause i know there are many data modeling and experts here what could help me find the right solution out of many possible ones.

I am working on a data model for a school management system data model which will be for a school district (means many schools will use the same model). But i have some concerns and questions and decided i should ask before i decide on something. 1) Is using the same database for 20+ schools through one web interface a good design? If not any suggestions?

2) With the structure above there will be 800,000+ rows just for attendance only in one year(considering 7 periods and %10 attendance recordings). Would this be a problem? Any ideas?

3) And last question is about creating an academic year and school filter. What would be the best way to filter records for schools and academic years? For example; out of 3 years of total records how would the data model be to filter the attendance records or school roster for SchoolX and academic yearX?

thanks in advance -shane

Hi Shane,

Before one can answer your questions, you should first show the model you have designed. You probably have School, Tutor, Pupil, Class, etc. in your logical datamodel.

It would also be nice to know which database (SQLServer, SQLServer Express, Oracle, DB2, ..) you are planning to use and some info on the number of schools (20?), tutors, pupils, etc.

For a database such as SQLServer or Oracle the numbers you mention are not likely going to be a problem.

Regards,

Jan

Thank you for writing back JayBee, my design is getting bigger and bigger but the solution i m thinking to my question is below. i dont know if this is the best solution but seems like one to me. and answers to your questions are also below. thanks again

JayBee wrote:

Hi Shane,

Before one can answer your questions, you should first show the model you have designed. You probably have School, Tutor, Pupil, Class, etc. in your logical datamodel. Jan

yes there is school, people (which has student, teacher,parent as subtypes), room, course, class etc in the model. the solution i m thinking is using a third table for the main tables like people table or course table such as PeopleAcademicYear which will keep the PeopleID and AcademicYearID as primary keys. if same person is enrolled for the next school year there will be one more row created for him in PeopleAcademicYear table. Same for Courses or Attendance or Assignments etc.

JayBee wrote:

It would also be nice to know which database (SQLServer, SQLServer Express, Oracle, DB2, ..) you are planning to use and some info on the number of schools (20?), tutors, pupils, etc. Jan

Will be using sqlserver express and will switch to sqlserver depending on the size and needs in the future. There will be 20 schools at the beginning which may go up to 50 in the next 5 years. Total staff will be around 600 at the beginning and may go up to 1500 in the next 5 years. Total number of students will be around 5000 and may go up to 15000 in the next 5 years. These numbers are not big numbers but again the attendance records by itselt will be close to million rows at the end of the first yeat with 20 schools and 5000 students. This may go up to 3-5 million rows just for the attendance in the next 5 years.

Again i dont know if my design is a good design and dont want to make the mistake of starting with a wrong design and getting stuck in 2 years. Thanks again

-shane

Have you made an ERD of your datamodel and can you post it? Without knowing the structure of your datamodel and some details on the fields, keys / indexes it is not possible to judge the design.

Express can support databases upto 4GB in size. Probably large enough for your needs.

JayBee wrote:

Have you made an ERD of your datamodel and can you post it? Without knowing the structure of your datamodel and some details on the fields, keys / indexes it is not possible to judge the design.

Express can support databases upto 4GB in size. Probably large enough for your needs.

Thanks you/ right now i have 42 entities and it seems i ll end up with around 60 entities when i am finished. it looks pretty complex to me which i believe is nothing to many data modeling experts. i am using the trial version of er/studio so i cant post the erd, it wont let me.

It looks like i found a solution which isnt bad. I ll write back if something i do doesnt make sense.

thanks again

-shane

JayBee wrote:

Express can support databases upto 4GB in size. Probably large enough for your needs.

I would be more concerned about Express being limited to 1 CPU and 1 GB of RAM. Your likely to be less than impressed with performance if your expecting heavy use of the system.

I do not think that Express is going to be the problem. Depends on the OS used( XP, Vista, WS2000K)