Help with theory... or something

[coalduststar]

Hi, i'm looking if someone can point me in the right direction. I have done database design as part of my course at university and am familiar with most of the terminology but i'm not particularly rational or logical so I can't really progress this schema from what I have with out someone having a look at it.

 

Basically, there's a charity and they send officers to run programs (set of activities) at schools.  The database needs officer info, school info and of course program info.  Each program runs for a period of time and can be running at more than one school so i think my tables wil be (i think):

 

School,

school_ID

name

program_ID

address

post_code

etc...

 

Program,

program_ID

officer_ID

school_ID

name

manager

date_start

date_fin

activities

comments

etc...

 

Officer,

officer_ID

name

etc..

 

This would be fine if the a program couldn't occur in multiple schools over a period of time. I'm not sure how to proceed from here cos that would mean a program could have multiple school_IDs or that duplicate program_IDs should be allowed. Does this not stuff up the data structure?

 

Hopefully i'm not showing my ignorance up too much but the time for pride has passed!! Any help would be awesome- thanks in advance.

 

 

 

[effigy]

I would keep the information separate: School, Program, and Officer only contain information pertaining to themselves, i.e., there are no foreign keys. Afterwards, make a table which connects them:

School_Program_Officer

======================

id

school_id

program_id

officer_id

...any other metadata...

[coalduststar]

ok- not meaning to sound ignorant here but how would you do that? I always thought the tables had to be linked with foreign keys. Of course I probably wasn't paying attention because I'm a bit lost.

 

So i make a 4th table which combines all the data? Does that affect the databases's referential integrity? I've really only ever used mySQL to make databases that are just like temporary data storage or for dynamic content on a website. 

 

Apologies, i'm a total idiot when it comes to this.

[effigy]

I always thought the tables had to be linked with foreign keys.

 

Correct. I'm saying your main tables (namely Programs) will not contain foreign keys as they did in your example. This design does not allow one program to be attached to more than one school without creating duplicate information. (Unless I am misunderstanding how these function.)

 

So i make a 4th table which combines all the data? Does that affect the databases's referential integrity?

 

Right. The integrity will remain because the new table will have its own primary key--this, arguably, isn't even needed--plus three foreign keys, all which reference one of the main tables.

 

Think about it this way:

 

Let's say you want to associate... colors to fruits. You'd have a Colors table (id, name) and a Fruit table (id, name):

Colors

======

id | name

1  | Red

2  | Green

 

Fruits

======

id | name

1  | Apple

How do you easily indicate that there are green and red apples without polluting either table with outside information? You make another one, linking them together:

Fruits_Colors

=============

fruit_id | color_id

1      | 1

1      | 2

[coalduststar]

Thanks, that explains things a bit better- i have a neuron or two firing at some kind of vague recollection of this! So when you're querying that table it will be able to access all that other information, just by having those foreign keys?

 

What I mean is, will that table allow me to access and amend those other tables or is that just for displaying data?

[effigy]

Foreign keys connect one table to another, and this goes one step further, using multiple foreign keys to connect multiple tables together. No matter what you're working with (e.g., Colors, Fruits, Fruits_Colors), as long as you have the id, you have the ability to mold whichever table you need.