June 16, 2010 at 1:11 am
I design this database schema for a car incident ;
The sftware should produce statistical reports about incident (victims distinct by incident type, by road type, by vehicle type, etc).
i'd like to know your opinion about it.
I'm not sure about the entity vehicle (my teacher always said that if an entity has no significant attribute probably is not an entity).
As you can see i'm not interested in the specific vehicle (i don't store any of the vehicle specific details) but only vehicle type, but i need to know how many distinct vehicle of that tpe are involved in the accident and if its occupant are injured or killed.
example:
VehicleType - Number of vehicle - Injured - Killed
Car - 2 - 3 -1
Truck - 1 - 1 - 0
Bycicle - 1 - 0 - 1
June 16, 2010 at 8:51 am
I think you may have more critical issues to look at.
Example:
IncidentTable one-to-many PersonTable
Question:
How do you link these two tables? which column/s hold the relationship?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 16, 2010 at 12:51 pm
The Person table have a IncidentId foreign Key (foreign keys are not shown in the diagram).
The IncidentFactor table in the diagram is wrong, its fields are IncidentFactorID (PK), ContributingFactorId (FK)
I'm thinking to add two relationship :
- a one-to-one relationship between Vehicle table and IncidentFactor table (i'm not sure if its necessary to store which vehicle has done what)
- a one-to-many relationship between Person and IncidentFactor because i need to know,for example, the psychophysical state of the driver or his behaviour.
June 16, 2010 at 1:34 pm
liuc (6/16/2010)...foreign keys are not shown in the diagram
Would you mind in showing relationships? othewise it gets real hard to understand navigation.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 17, 2010 at 4:14 am
Yes, i omitted the foreign key field in the existing table. relationship are all shown (except the two i was talking about in the previous post).
As soon as possible i will post au updated an detailed erd (with Foreign keys)
June 18, 2010 at 9:09 am
Here is the latest version of my db. I've attached also a possible (partial) extension of it.
Please feel free to give feedback.
June 18, 2010 at 11:16 am
Doesn't show cardinality, sorry - can't read it yet 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 18, 2010 at 12:53 pm
Probably this is the best that i can do with visio for cardinality.
June 21, 2010 at 8:21 am
I think showed relationship between IncidentTable and RoadUserTable is not correct or doesn't exist at all.
Please validate all relationships.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 21, 2010 at 9:42 am
It's necessary because an incident may involve zero or more pedestrians.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply