coalduststar Posted September 4, 2007 Share Posted September 4, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/67911-help-with-theory-or-something/ Share on other sites More sharing options...
effigy Posted September 4, 2007 Share Posted September 4, 2007 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... Quote Link to comment https://forums.phpfreaks.com/topic/67911-help-with-theory-or-something/#findComment-341390 Share on other sites More sharing options...
coalduststar Posted September 5, 2007 Author Share Posted September 5, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/67911-help-with-theory-or-something/#findComment-341709 Share on other sites More sharing options...
effigy Posted September 5, 2007 Share Posted September 5, 2007 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 Quote Link to comment https://forums.phpfreaks.com/topic/67911-help-with-theory-or-something/#findComment-341802 Share on other sites More sharing options...
coalduststar Posted September 5, 2007 Author Share Posted September 5, 2007 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? Quote Link to comment https://forums.phpfreaks.com/topic/67911-help-with-theory-or-something/#findComment-341941 Share on other sites More sharing options...
effigy Posted September 5, 2007 Share Posted September 5, 2007 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. Quote Link to comment https://forums.phpfreaks.com/topic/67911-help-with-theory-or-something/#findComment-342075 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.