Hello, i need help with a database model which i need to complete. The scenario is about racehorses. Racehorse will have an owner, trainer, stable, and parents who are also racehorses, i also need to add where the horse has run and at what racecourse and its position and jockey who rode it. I understand how to add most of this to a model, however i am having trouble doing the races and parents. What entity and attributes could i have for these? thanks in advance and any help would be appreciated, thanks!
I does not sound like something difficult - it sounds like just a standard database scenario. The race is a table with details and you simply link the horse id to it with the id key of the race using a link entity table (separate table) which could be very basic - like simply 2 foreign keys. The most basic race record would simply be the date/ time/ location/ rider. The details of that table are not of concern in relation to the link. You just need an id for use as a foreign key.
With regards to parents you would assume that it is one field for each inside the horse details table. Father and Mother unless you are planning on having some kind of alien horse with 3 or more parents. The parent will simply be another "Horse" - it will be the same as the child (obviously it cannot be its own child!)
You are simply relating them to each other via an id. Neither of the parents should technically be "optional" but it could be "unknown". That would mean you would need a value for "unknown". The reason I say that is because I do not believe most systems allow a foreign key field to be null - it may not be a violation (depending on the system) but I think a value should be given even if it implies "unknown". At the very start you would have no horses anyway so this precludes that you would need at least one default value like "unknown".
Over time it would cease to be a problem because the race horses will have children which means that there will be horses available to be parents.
On the other hand, in relation to parents - maybe that parent is NOT a part of your system. That would mean that you would need to indicate whether the parent is one of your horses (which will have records in the database already) OR someone else's horse!
You could alternatively create a link entity table that allows you to add ALL the horses relations (including brothers and sisters) and use a lookup value for the relation like "mother", "father", "sister" etc.
Those are some things to think about anyway (from the perspective of a database designer).