Database Design Preference

mr_lasseter

Which do you prefer?

Items to store:
Human
BirthDate
Name
Age
PhoneNumber
Race

Dog
BirthDate
Name
Age
Breed
Color

Tables:

Option1
Animal (Id, BirthDate, Name, Age)
Human (AnimalId, PhoneNumber, Race)
Dog (AnimalId, Breed, Color)

Or

Option 2
Human (HumanId, BirthDate, Age, PhoneNumber, Race)
Dog (DogId, BirthDate, Age, Breed, Color)

Mike Lasseter

Pete OHanlon

I hope you're wearing your flame retardant underwear here. This should either be in the Design section or the SQL/ADO section. I bet you can feel the flames licking at your butt right now.

Deja View - the feeling that you've seen this post before.

My blog | My articles

John M Drescher

mr_lasseter wrote:

ail has been down for a couple hours now

I use Option1 most of the time.

John

mr_lasseter

Yeah, I thought of that before I posted. But I have seen many preference questions in the lounge. Oh well, flame if you must I don't think it will hurt my feelings.

Mike Lasseter

leckey 0

I like 1 but have used 2. And I agree that since this is more of a polling question, it's valid in the Lounge.

http://craptasticnation.blogspot.com/[^]

Marc Clifton

If you have the birthdate, why do you need the age? Or are you planning for the invention of time travel? And what about my pet rock? It has a birthdate and a name, but it's not an animal. Same with my beeniebaby children. They're not animals! But, since you ask, and we're on the path of wierd anyways: table 1: Entity table 2: Entity-Attribute values table 3: attributes table 4: Master Entity Attributes That way, you can add more associations to your entities (like cats, we can't forget cats can we, I mean, my cats are really annoyed right now that only dogs were mentioned) and more attributes, like "sex now", "sex at birth", "sex ever". Marc

Thyme In The Country
Interacx
My Blog

Douglas Troy

Neither, I like this better: Animal Table (generic common attributes) AnimalID, Animal_SpeciesID, AnimalDOB, Animalcolor, AnimalGender Species Table (aka "Human", "Dog", "Cat", "Bob") SpeciesID, SpeciesName, SpeciesDesc, Species_BreedID Breed Table ("Pitbull", "Retriever") BreedID, BreedName, BreedDescription My 2½ cents.

:..::. Douglas H. Troy ::..
Bad Astronomy |VCF|wxWidgets|WTL

mr_lasseter

Marc Clifton wrote:

like cats, we can't forget cats can we, I mean, my cats are really annoyed right now that only dogs were mentioned

I'm allergic to cats so I am a little biased.

Mike Lasseter

leckey 0

I'm allergic and have 3.

http://craptasticnation.blogspot.com/[^]

Pete OHanlon

Marc Clifton wrote:

Same with my beeniebaby children. They're not animals!

They're not. They have feelings too.

Deja View - the feeling that you've seen this post before.

My blog | My articles

Douglas Troy

leckey wrote:

I'm allergic and have 3

For some odd reason, your comment reminded me of this The Difference[^]

:..::. Douglas H. Troy ::..
Bad Astronomy |VCF|wxWidgets|WTL

leckey 0

Shouldn't it remind you of this? http://xkcd.com/231/[^]

http://craptasticnation.blogspot.com/[^]

Member 96

The annoying answer is "it depends" :) Option 1 seems like a natural preference and I'd take it 9 times out of 9.5 (perhaps even broken out into more tables), but fairly regularly I've run across situations where Option 2 actually fits a given situation better and here's a real world example "ripped from the headlines" (well actually from something I'm doing right now) In fact I have been spending the last few days turning an option 1 into an option 2 in a released application! We originally overthought the design for the contact portion of our application. The contacts are not the most important part, it's not a contact management app, but someone thought it was apparently back in the design stage a few years ago. We have various objects, let's call one of them a customer, anther a vendor etc that can have one or more contacts attached to them. The contacts collection are shared by the different objects. A contact is just a person's name and phone / email record. We had a hugely overwrought system that included a contacts table and a contact phones table corresponding to a grid in the UI that allowed for unlimited number of contacts with separate fields for title, job title, first name, last name, an unlimited sub phones collection under that with country code, area code, phone number, extension phone type (fax, pager etc). Straight out of a text book in terms of normalisation and perfectly wrong for an application that is primarily about other things and not contacts. One contact and one phone number had to be the "primary" ones so we knew what to pipe to the reports (no one want's to print a simple invoice with a dozen phone numbers on it etc) which led in turn to another pile of code to ensure there is always a default contact, default phone, lot's of UI code to deal with the user checkboxing the default and walking the collections to make sure no one else was also the default. It's amazing how it all adds up when you put it into practice. This one decision led to a mess at the entry level, poor performance in the reporting and listing level etc. Reports and lists are big fans of flat data and horrible to work with hiearchical data. Hiearchical data (which is what option 1 triggers up the layers of a typical application) can sometimes be detrimental in unexpected ways. I'm a big fan of irreducable simplicity (as simple as possible but no simpler) when it comes to usability, I don't know where our minds were at the time this was originally designed; I highly suspect one use probabl

Douglas Troy

hahaha Hadn't seen that one before. classic.

:..::. Douglas H. Troy ::..
Bad Astronomy |VCF|wxWidgets|WTL