Database design question
Suppose you were designing a database to store accidents, and you had the need to categorize them, are the categories below sufficient? By accident here I mean non-intentional occurrences which lead to undesirable consequences... 1. Vehicle Accidents involving vehicles with at least 4 wheels 2. Cycles (is there a better name for this?) Vehicles with three wheels and less 3. Buildings 4. Infrastructure, e.g. bridges collapsing etc 5. Marine How do you differentiate someone drowning from a boat sinking? 6. Aircraft 7. Ordinance? Old military appliances that tend to explode? 8. Domestic Accidents which happen at home. But if a fire takes place at home, and a fire takes place at a factory, are these two different categories or should they be the same, or both? 9. Industrial
you are looking for one general category called "Incident" this will be your event where you can capture the above ....Borrowed from MSCRM ....if your oranisation would like they can adopt a CRM system ...cuts work by 50% percent :) On Tue, Oct 25, 2011 at 10:29 AM, Peter Karunyu <pkarunyu@gmail.com> wrote:
Suppose you were designing a database to store accidents, and you had the need to categorize them, are the categories below sufficient? By accident here I mean non-intentional occurrences which lead to undesirable consequences...
1. Vehicle Accidents involving vehicles with at least 4 wheels
2. Cycles (is there a better name for this?) Vehicles with three wheels and less
3. Buildings
4. Infrastructure, e.g. bridges collapsing etc
5. Marine How do you differentiate someone drowning from a boat sinking?
6. Aircraft
7. Ordinance? Old military appliances that tend to explode?
8. Domestic Accidents which happen at home. But if a fire takes place at home, and a fire takes place at a factory, are these two different categories or should they be the same, or both?
9. Industrial
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- GG
Hi, The best approach is make it flexible and configurable Create a table called Accident Categories. Let the users define the categories themselves Then on the accidents table, link an accident with a category Regards Shadrack ________________________________ From: geoffrey gitagia <ggitagia@gmail.com> To: Skunkworks Mailing List <skunkworks@lists.my.co.ke> Sent: Tuesday, October 25, 2011 10:55 AM Subject: Re: [Skunkworks] Database design question you are looking for one general category called "Incident" this will be your event where you can capture the above ....Borrowed from MSCRM ....if your oranisation would like they can adopt a CRM system ...cuts work by 50% percent :) On Tue, Oct 25, 2011 at 10:29 AM, Peter Karunyu <pkarunyu@gmail.com> wrote: Suppose you were designing a database to store accidents, and you had the need to categorize them, are the categories below sufficient? By accident here I mean non-intentional occurrences which lead to undesirable consequences...
1. Vehicle Accidents involving vehicles with at least 4 wheels
2. Cycles (is there a better name for this?) Vehicles with three wheels and less
3. Buildings
4. Infrastructure, e.g. bridges collapsing etc
5. Marine How do you differentiate someone drowning from a boat sinking?
6. Aircraft
7. Ordinance? Old military appliances that tend to explode?
8. Domestic Accidents which happen at home. But if a fire takes place at home, and a fire takes place at a factory, are these two different categories or should they be the same, or both?
9. Industrial
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- GG _______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
@Shadrack, unfortunately, I cannot allow the users to create the categories since, well, users never know what they really want. Secondly, at the end of it all, I want to create nice bar graphs comparing motorcycle and motor vehicle accidents. Since it will be running on MySQL, I am assuming that at some point I will have to do a COUNT() followed by GROUP BY category, unless there is an alternative to that. Another question, which is the best way to deal with accidents which span several categories? On Tue, Oct 25, 2011 at 11:27 AM, Shadrack Mwaniki < shadrack_mwaniki@yahoo.com> wrote:
Hi, The best approach is make it flexible and configurable Create a table called Accident Categories. Let the users define the categories themselves Then on the accidents table, link an accident with a category
Regards Shadrack
------------------------------ *From:* geoffrey gitagia <ggitagia@gmail.com> *To:* Skunkworks Mailing List <skunkworks@lists.my.co.ke> *Sent:* Tuesday, October 25, 2011 10:55 AM *Subject:* Re: [Skunkworks] Database design question
you are looking for one general category called "Incident" this will be your event where you can capture the above ....Borrowed from MSCRM ....if your oranisation would like they can adopt a CRM system ...cuts work by 50% percent :)
On Tue, Oct 25, 2011 at 10:29 AM, Peter Karunyu <pkarunyu@gmail.com>wrote:
Suppose you were designing a database to store accidents, and you had the need to categorize them, are the categories below sufficient? By accident here I mean non-intentional occurrences which lead to undesirable consequences...
1. Vehicle Accidents involving vehicles with at least 4 wheels
2. Cycles (is there a better name for this?) Vehicles with three wheels and less
3. Buildings
4. Infrastructure, e.g. bridges collapsing etc
5. Marine How do you differentiate someone drowning from a boat sinking?
6. Aircraft
7. Ordinance? Old military appliances that tend to explode?
8. Domestic Accidents which happen at home. But if a fire takes place at home, and a fire takes place at a factory, are these two different categories or should they be the same, or both?
9. Industrial
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- GG
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- Regards, Peter Karunyu -------------------
Accidents have a unique ID, hence when counting you can ensure that you do not count an ID twice. -- with Regards: blog.denniskioko.com <http://www.denniskioko.com/>
Once again ..whats wrong with using a CRM system? open source or otherwise...or copy the logic that they use... On Tue, Oct 25, 2011 at 3:39 PM, Dennis Kioko <dmbuvi@gmail.com> wrote:
Accidents have a unique ID, hence when counting you can ensure that you do not count an ID twice.
-- with Regards:
blog.denniskioko.com <http://www.denniskioko.com/>
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- GG
You can always add categories later if you come across an unhandled category. For the question of an accident being in more than one category, you can introduce a table to map accidents to a category resulting in 3 tables 1. Accident - contains the accident details, time, location... Blah blah blah 2. Categories - contains ur accidents defination 3. AccidentMappings - maps an accident to a catrgory, contains 2 foreign keys categoryID, AccidentID . Add a unique key on (categoryID,AccidentID) Sent from my BlackBerry® -----Original Message----- From: Peter Karunyu <pkarunyu@gmail.com> Sender: skunkworks-bounces@lists.my.co.ke Date: Tue, 25 Oct 2011 11:40:02 To: Shadrack Mwaniki<shadrack_mwaniki@yahoo.com>; Skunkworks Mailing List<skunkworks@lists.my.co.ke> Reply-To: Skunkworks Mailing List <skunkworks@lists.my.co.ke> Subject: Re: [Skunkworks] Database design question _______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://orion.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
@peter, the logic of structuring categories as entries in a table is one very sound one, If i were you i would not ignore it (unless this piece of software is what my peeps call <hit and run >, design the system with future needs in mind, create a table for categories, give them a unique id and in your query do a count where category id is used to deal with duplicates. On Tue, Oct 25, 2011 at 8:24 PM, <mwirigic@gmail.com> wrote:
You can always add categories later if you come across an unhandled category. For the question of an accident being in more than one category, you can introduce a table to map accidents to a category resulting in 3 tables
1. Accident - contains the accident details, time, location... Blah blah blah
2. Categories - contains ur accidents defination
3. AccidentMappings - maps an accident to a catrgory, contains 2 foreign keys categoryID, AccidentID . Add a unique key on (categoryID,AccidentID)
Sent from my BlackBerry®
-----Original Message----- From: Peter Karunyu <pkarunyu@gmail.com> Sender: skunkworks-bounces@lists.my.co.ke Date: Tue, 25 Oct 2011 11:40:02 To: Shadrack Mwaniki<shadrack_mwaniki@yahoo.com>; Skunkworks Mailing List< skunkworks@lists.my.co.ke> Reply-To: Skunkworks Mailing List <skunkworks@lists.my.co.ke> Subject: Re: [Skunkworks] Database design question
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke _______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- *“The twentieth century has been characterized by three developments of great political importance: the growth of democracy, the growth of corporate power, and the growth of corporate propaganda as a means of protecting corporate power against democracy”* ~ Alex Carey ~ Tel No: 0x2af23696
interesting MS SQL does allow to store coordinates like GPS and reporting services i think can do reports by maps ..... cant quite remember where i read that ... Any ways you can pull reports on a map that show in which location which types of accidents happen ...inturn it will help traffic police to put up proper road signs (dont any one burst my bubble) and maybe shre you data with car GPS systems provider like Garmin and people can be aware of the danger spots (i think the black spot warning sign that usually is knocked down dont do much good ) On Tue, Oct 25, 2011 at 10:17 PM, [ Brainiac ] <arebacollins@gmail.com>wrote:
@peter, the logic of structuring categories as entries in a table is one very sound one, If i were you i would not ignore it (unless this piece of software is what my peeps call <hit and run >, design the system with future needs in mind, create a table for categories, give them a unique id and in your query do a count where category id is used to deal with duplicates.
On Tue, Oct 25, 2011 at 8:24 PM, <mwirigic@gmail.com> wrote:
You can always add categories later if you come across an unhandled category. For the question of an accident being in more than one category, you can introduce a table to map accidents to a category resulting in 3 tables
1. Accident - contains the accident details, time, location... Blah blah blah
2. Categories - contains ur accidents defination
3. AccidentMappings - maps an accident to a catrgory, contains 2 foreign keys categoryID, AccidentID . Add a unique key on (categoryID,AccidentID)
Sent from my BlackBerry®
-----Original Message----- From: Peter Karunyu <pkarunyu@gmail.com> Sender: skunkworks-bounces@lists.my.co.ke Date: Tue, 25 Oct 2011 11:40:02 To: Shadrack Mwaniki<shadrack_mwaniki@yahoo.com>; Skunkworks Mailing List<skunkworks@lists.my.co.ke> Reply-To: Skunkworks Mailing List <skunkworks@lists.my.co.ke> Subject: Re: [Skunkworks] Database design question
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke _______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- *“The twentieth century has been characterized by three developments of great political importance: the growth of democracy, the growth of corporate power, and the growth of corporate propaganda as a means of protecting corporate power against democracy”*
~ Alex Carey ~
Tel No: 0x2af23696
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- GG
@geoffrey, EXACTLY! :-) On Wed, Oct 26, 2011 at 8:23 AM, geoffrey gitagia <ggitagia@gmail.com>wrote:
interesting MS SQL does allow to store coordinates like GPS and reporting services i think can do reports by maps ..... cant quite remember where i read that ... Any ways you can pull reports on a map that show in which location which types of accidents happen ...inturn it will help traffic police to put up proper road signs (dont any one burst my bubble) and maybe shre you data with car GPS systems provider like Garmin and people can be aware of the danger spots (i think the black spot warning sign that usually is knocked down dont do much good )
On Tue, Oct 25, 2011 at 10:17 PM, [ Brainiac ] <arebacollins@gmail.com>wrote:
@peter, the logic of structuring categories as entries in a table is one very sound one, If i were you i would not ignore it (unless this piece of software is what my peeps call <hit and run >, design the system with future needs in mind, create a table for categories, give them a unique id and in your query do a count where category id is used to deal with duplicates.
On Tue, Oct 25, 2011 at 8:24 PM, <mwirigic@gmail.com> wrote:
You can always add categories later if you come across an unhandled category. For the question of an accident being in more than one category, you can introduce a table to map accidents to a category resulting in 3 tables
1. Accident - contains the accident details, time, location... Blah blah blah
2. Categories - contains ur accidents defination
3. AccidentMappings - maps an accident to a catrgory, contains 2 foreign keys categoryID, AccidentID . Add a unique key on (categoryID,AccidentID)
Sent from my BlackBerry®
-----Original Message----- From: Peter Karunyu <pkarunyu@gmail.com> Sender: skunkworks-bounces@lists.my.co.ke Date: Tue, 25 Oct 2011 11:40:02 To: Shadrack Mwaniki<shadrack_mwaniki@yahoo.com>; Skunkworks Mailing List<skunkworks@lists.my.co.ke> Reply-To: Skunkworks Mailing List <skunkworks@lists.my.co.ke> Subject: Re: [Skunkworks] Database design question
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke _______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- *“The twentieth century has been characterized by three developments of great political importance: the growth of democracy, the growth of corporate power, and the growth of corporate propaganda as a means of protecting corporate power against democracy”*
~ Alex Carey ~
Tel No: 0x2af23696
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
-- GG
_______________________________________________ Skunkworks mailing list Skunkworks@lists.my.co.ke http://lists.my.co.ke/cgi-bin/mailman/listinfo/skunkworks ------------ Skunkworks Rules http://my.co.ke/phpbb/viewtopic.php?f=24&t=94 ------------ Other services @ http://my.co.ke
participants (6)
-
[ Brainiac ] -
Dennis Kioko -
geoffrey gitagia -
mwirigic＠gmail.com -
Peter Karunyu -
Shadrack Mwaniki