If there is a better forum for this then can you point it out to me :)
I am writing the database in SQLServer, but it is a non-specific question I have.
Ok so here is the question.
The database is to store some information about birds.
I have a table called 'bird' (I am using singular table names... I think it is wrong but it is the way-it-is-done here!)
I have a second table "RandomSample" which holds some data from the bird that can be taken at any time (and multiple times in a survey)
Easy enough so far... however for male and female birds some different data is recorded. I don't want to just splurge the rows into the "RandomSample" table, so I thought I could add a "maleData" and femaleData" table to link to the "RandomSample" table.
The "bird" table contains the sex of the bird (as this does not change) so with the four table layout it would be possible to have the "sex" in the "bird" table as "female" but still have data in the "maleData" table.
Of course I would enforce the data entry in the interface, but I would prefer to have the database be unable to store contradictory information.
I hope I have given enough info to explain my problem... now should I worry or not? what is the best practice here?
Thank you very much for your time reading!