Right first time!?!

[samonwalkabout]

Im begining work on a new database and would like to get the structer and relationships right first time( for a change).

Basically its a simple training request database.

It will allow users to request

--Individuals to go to a particular training event.
(TrimID identifys user)

--Groups to go to an event
(Groups of trim ID's)

--Departments to go to an event.
(Trim ID's grouped into departments)

I also need to be able to add a group of trims and then remove several from the event (but not the group itself)

Attached is a picture of my realtionships i think i have got mostly everything i need just not 100% sure on the add a group of people but remove some of the individuals problems.

 

[GumbyD]

Are the Trim's ever in more than one group or Department? You have a Join circle which always sets off alarms in my brain. It may not be a problem here because you do not have referential Integrity set for that table, but a compound key might be a better solution if a Trim can only be in one group or Department.

GumbyD

 

[samonwalkabout]

Yes a Trim can be in several groups and in several departments.

does that mean its okay?

Still can work out how to add a whole group of trims and then remove several trims with out removing the group, unless i can use a query to filter out certain trim numbers without effecting the group structer in the long term.

 

[GumbyD]

I guess I pictured something more along the lines of having a trim table with a single key associated to trimdepartment and trimgroup tables with compound keys of TrimID & DeptID or TrimID and GroupID. It is hard to say if your design will have issues or not, a lot of that depends on how you are inserting data. I personally try not to create joins where I am not able to inforce referential integrity, just because I like to have that security of knowing I am not allowing orphans into the tables.

As for your updating and deleting of data - how were you going to handle that in a macro or in code? I think this is where you are going to create orphan records with the joins set up the way they are. Maybe if you posted some data for these tables and a bit more information on how you want the data to work (ie - what data is entered first? how does data get to related tables?...)

GumbyD

 

[samonwalkabout]

Hey gumbyD thanks for your input, could you draw me a picture of what you mean about the table structer?

The data will be added via forms and i dont see that anything will be deleted as its a rolling database for the next few years. the main tables

tblMainrequest - this is the main table i am using coldfusion to a web front end, but in effect its a simple form with drop downs for each field except comments.

tblrequesttype- just three records single, group, deptartment

tblevents - will contain ifno on upcoming events added as they come up by a admin user.

tblsuper- list of supervison names

tblgroup list of groups that can be added to

tblTrimNumber list of trim numbers and names

-------------------------------------------------
then sub tables of tbldepmem a table showing dept and trim, so people in each department.

and tblgroupmem a table showing group and trim, so people in each group.

Its the final two that im not sure about, i know that this will mean that i can add a group ID to an event and can show all those people at the event. but am confused about what to do if i want to then remove some of those people from the event but not the group.

any help is great

thanks

 

[GumbyD]

simon -

I must be dumb or something, but I am not sure how to attach an image to one off this posts. I press the IMG button but it gives me an http prompt??? You posted an image, what's the trick?

GumbyD

 

[samonwalkabout]

Look down below the box where you enter the txt there is a Options section and then an Attach file section. Just browse to it from here and attach.

Thanks

 

[samonwalkabout]

I have been looking around at some other posts and have got a better idea about many-many realtionships than before think i have solved my earlyer problems. I belive i have a structer that will allow me to do everything i want.


Thanks for the help GumbyD

Sam

 

[GumbyD]

Sam -

Glad you got you want! I do have a screen shot of the structure I had in mind if you still want to see it.

GumbyD

 

[samonwalkabout]

Definately, i can never get too much help with these things

 

[GumbyD]

Here you go.

 

[samonwalkabout]

Great Stuff!!

That's basically solution i came to in the end aswell. Good to know im starting out with the correct structer, I think i finally understand the use of Many-Many relationships! Thats one of the major problems with begin self taught i have some gaps in my fundimental knowledge. Thank god for this forum!

Thanks