Schema help.

[fragglerock]

I am designing a databse and have a question about my design.

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!
Fragg.

 

[Olaf Doschke]

Hello Fragg!

What's the relation between RandomSample and maleData or femaleData? Is it 1 Randomsample + 1 maleData xor 1 femaleData (1:1), can there be N records in either male- or femaledata (1:n)? Can there be Randomsample data without male- or femaledata (1:0-1)?

Depending on that you could let maleData and femaleData point to the bird with a BirdID foreign key instead of binding RandomData to the bird. That makes it easier to make the constraint.

And then let Randomdata point to either of the male- or femaledata table by two foreign key fields with the constraint that only one must be set.

The disadvantage is, it's not that easy to list the commondata of all birds, you would need a union of the male and the female branch to the Randomsample data.

Bye, Olaf.

 

[fragglerock]

I had envisioned it,

Bird 1-many RandomData
RandomData 1-1 maleData
RandomData 1-1 femaleData

I think flipping it as you suggested would make it unnecessarily opaque and queries a little too complex... I need to keep things simple for my poor brain! A good thought though and one that had not occurred to me... I need more practice at this!

I think I will go with the simple schema and be careful in the data entry!

Thanks again.

 

[ChrisHunt]

How many gender-specific fields are there in your proposed two tables, how many gender-neutral ones are there in the RandomSample table?

If there are only a few of the former, and lots of the latter, I suggest that it's overkill to complicate the data structure by adding the gender-specific tables. Sometimes it's sensible to compromise the purity of your data structure in the interests of practicality, I suspect that this may be one such case.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Olaf Doschke]

Hi Chris,

that's a good point, especially as the database is SQL Server. If you set either male or female data fields NULL, you even only waste neglectible discspace due to the way SQL Server stores records.

Even if you don't make constraints it's much easier to determine if there is ambiguous data, if it's all in one table.

Bye, Olaf.

 

[Crowley16]

if the value of male/female doesn't change, then would it not be best to put that into the bird table, rather than the randomData table?


--------------------
Procrastinate Now!

 

[fragglerock]

Sex is of course in the bird table, sorry if that was not clear.

There are 12 bits of info for both sexes, an extra 14 for males, and only an extra one bit for females.

I see the point of the simpler one table solution I think that that may be the way I go. Certainly for this disc space is well down the list of concerns. I think easiness to work with is high up the list!

Thanks very much for your thoughts and input.

 

[ChrisHunt]

Incidentally, I think "RandomSample" is a poor name for this entity. It implies that rows of the table have something to do with the sample process as a whole, rather than being an individual measuring event for a bird.

You're taking a random sample of birds and doing something to them. That "something" is what you should call the table.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[Thargy]

fragg,

your quite right, it's inherently daft to split such data. Just have one table, with a bit field indicating gender. That way, querying for all male birds with feature xyz is easy.

SELECT * FROM FRAGGS_TABLE WHERE MALE = TRUE AND MY_PARAMETER = 'XYX'

Unless you are in a data warehouse situation, de-normalising by design is usually not a good idea. Keep every type of information in one place and one place only. That was, when you fix a problem in that one place, you've fixed it everywhere - guaranteed.

I wecome the fact that you're sufficiently concerned with design to try and anticipate problems before they arise - well done - many can't be bothered.

Regards

T