Hi all,

I'm designing a little DB solution and seem to be stuck...
What I have so far is in an ERD jpg attached.

I basically need to capture the fact that delegates on the course instance might be a part of a group, with a designated group leader, allowing for more than one group to be on a course instance... The way I see it, seems wrong...
What do people think of that? Are there any other suggestions?

Thank you...

Few points:

• Strictly speaking that's not recursion. If groups could be made of other groups then that would be recursion.
• If this is homework then this should stated quite clearly before detailing your problem.
• I can't see any foreign key fields in any of your tables which makes it difficult (for me) to see how things tie together.
• Is GRP_Leader a field that holds the id of a delegate, a client or a trainer.
• Some details on where the course is being held etc might be useful.
• Could trainer and client be put into a common table with a type field?
• Not putting the details of the company that runs the courses into a table means that this stuff has to be hard coded into the programs - which is usually poor.

Oh, I did not realize that I had to say that it was homework...(Forum rules are quite concise...)

It is homework.

I suppose that lumping clients and trainers into 1 could be a sane idea.

The table company holds info about companies that a client might be booked by. So, the client table has a FK pointed at the companyID.

All courses are run by the same company.

One more thing, here is a select statement,
Below it is the output, maybe that'll make things a little clearer.
Output:

Also, see the new ERD where I have added FKs.

The FK fields themselves should really appear in the list of fields for the table. The text FK_Client_COL_Comp_4DGF684K tells me very little.

Actually the naming convention you use for your tables and fields could be improved. Would it be better to start a table name in upper case to distinguish it from the a field name. Why start all the field names with the code for the table? Looking at your SQL the DEL in delegate.DEL_GroupID tells me that the field is in the delegates table but then so does the word delegate! Wouldn't it be better to indicate which table this group id is in? I accept that the database is very small but I'm sure you could picture problems if the database had 100's of tables. It is good though to have standards so I'll commend you on that!

Just something to think about really. If you imagine the work involved in producing a screen etc to fully maintain two tables and provide searching etc then this work can be halved by having the data in one table. A primary aim of all databases is to logically organise your data so does it make sense to have tel numbers for one type of person in one table and tel numbers for other people in another table. If you just figure that clients and trainers are just types of people then you can put them in the same table. This isn't a hard rule but, as I say, it's worth thinking about.

The reason it's good to put the name of the company and any other details into a table is because the system may be used by other companies in future (unlikely with coursework I'll admit) or the company details may change to perhaps lot's of training companies might want to share the system. This is all straightforward to do if you have such a table but if you don't it means rewriting the code to reflect each change.

People put a lot of effort into helping others on the site. If they felt they were continually just helping students do their coursework then I'm sure you could understand that they'd offer their services less and the whole community would suffer. Obviously the aim of your coursework is to make you think rather than make me think. As you point out there are only 2 simple rules on the site which cover all occasions and as a moderator I'd like to ask you to just mention in any future questions that it's coursework (see rule 1)