I am currently trying to design a database system for a a school which arranges projects to take place with external companies for the students to work on over the summer.

I am currently having a few issues with my ERD. The main issue is weather or not to have a archive table within the database. I dont believe it should really be in there but, if i am to leave out the archieve table it leaves me with to smaller ERD models. Therefore there will be no link between them as show by the model below (via the link) (archive is still show but with no relationships)

http://img514.imageshack.us/my.php?image=erdmf8.jpg

If i am to leave it in i am going to end up with a many to many which is not going to work correctly. At the moment I am unable to think of any other tables which i could use to create a link.

Any help or views in to how this may be resolved will be much appreciated!

if you need any more information just let me know

(I am also aware that that the users to groups is the wrong way round in the image ;))

Yes, you do have some issues with your ERD. I can't see why you think they're related to auditing.

ERDs are about Analysis. Auditing is about Design (a different phase of software development).

Your auditing tables don't need to be in your analysis documentation and you definitely don't need to worry about referential integrity in auditing tables. They (many of them, one for each table being audited) are just a representation of what the database knew along the way about DML activity...no relationships...just raw data.

Now, about your ERD problems.

If I read your ERD correctly, It seems a student can be on one and only one project and that a project can have many students assigned. Is that accurate? If not, I suggest a M:M relationship there.

Additionally, your ERD indicates that a Supervisor can be on one or more projects and that a project can have only 1 supervisor (I have found this to be inaccurate in the real world). Is that correct?

Otherwise, the analysis portion looks reasonable.

Thanks for the reply and the comments so far! :D

Yes that is correct that a student can only be on one project but more than one student can be assigned to it. In the sense that they are not working together but can be assigned the same project to carry out.

With regards to the supervisors this is correct in that a supervisor can be assigned to many different projects but each project only has one supervisor.

From my understanding of ERDs forgive me if I am wrong is that my current ERD should not look like it is because at the moment it looks like there are two differant databases (hope that makes sense). Is this ok to have and if not are there any suggests how I could join it all together?

many thanks

Steven