×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Schema help.

Schema help.

Schema help.

(OP)
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.

RE: Schema help.

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.

RE: Schema help.

(OP)
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.

RE: Schema help.

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

RE: Schema help.

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.

RE: Schema help.

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!

RE: Schema help.

(OP)
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.

RE: Schema help.

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

RE: Schema help.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close