Suggesting a database schema...?

arnihr

I was wondering, how you hardcore mysql programmers would do this...

I have a database project, horse thing...

I've designed this like this:
table user
- primary key(user_id)
table horse
- primary key(horse_id)
table image
- id, not auto_increment, same in table horse

Now, there are more fields ofcourse, in all of those tables...

In table horse, all the information on each horse goes there, but I need to have information on mother/father/fatherofthefather/motherofthemother on each horse in table horse, but there's not much info om the "parents"
and the "grandparents"...

So I was just wondering, how would you see me build this up? 🙂

Any suggestions are well taken,
with thanks in advance, Arni

hotspur

I can see two ways of doing this,

The first would be to store the parent / grandparent info in the horse table. Each horse can only have two parents and four grandparents - you would have to allow for empty values for when you didn't know the details.

The second option would be to have a link table which contains three fields
Horse1 Horse2 relationship

ie horse_id 0001 horse_id 0002 realtionship Father - you could use a relationship code or store it as an ENUM type

The draw back with the second version is that if there is no data in the realtionship table, you would have to do an outer join and default to unknown

arnihr

I like the idea of two, because I've already done it the nr. 1 way 🙂

I guess I'm gonna do it the first...
Although I like the damn idea !! 🙂

Well, I'll play with this for a while before I make any decisions, and I'm also reading some articles on this subject...

Thanks, Arni

arnihr

Ok this is a schema for my db:

CREATE TABLE horse (
horse_id int(11) NOT NULL auto_increment,
owner_id int(11) DEFAULT '0' NOT NULL,
place varchar(50) NOT NULL,
name varchar(50) DEFAULT '0' NOT NULL,
breedingno int(11) DEFAULT '0' NOT NULL,
regno int(11) DEFAULT '0' NOT NULL,
age int(11) DEFAULT '0' NOT NULL,
gender varchar(6) DEFAULT '0' NOT NULL,
bcolor varchar(20) NOT NULL,
colorv varchar(20) NOT NULL,
description text NOT NULL,
price int(11) DEFAULT '0' NOT NULL,
type varchar(20) NOT NULL,
gaits varchar(15) NOT NULL,
date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
PRIMARY KEY (horse_id)
);

CREATE TABLE phorse (
phorse_id int(11) DEFAULT '0' NOT NULL,
f varchar(50) NOT NULL,
ff varchar(50) NOT NULL,
fm varchar(50) NOT NULL,
m varchar(50) NOT NULL,
mf varchar(50) NOT NULL,
mm varchar(50) NOT NULL,
f_breedingno int(11) DEFAULT '0' NOT NULL,
ff_breedingno int(11) DEFAULT '0' NOT NULL,
fm_breedingno int(11) DEFAULT '0' NOT NULL,
m_breedingno int(11) DEFAULT '0' NOT NULL,
mf_breedingno int(11) DEFAULT '0' NOT NULL,
mm_breedingno int(11) DEFAULT '0' NOT NULL,
fplace varchar(50) NOT NULL,
ffplace varchar(50) NOT NULL,
fmplace varchar(50) NOT NULL,
mplace varchar(50) NOT NULL,
mfplace varchar(50) NOT NULL,
mmplace varchar(50) NOT NULL
);

Where phorse_id int(11) DEFAULT '0' NOT NULL, = horse_id int(11) NOT NULL auto_increment,...

Is this a wise thing to do?
Or should I just mix the whole thing together in one table?

Thanks, Arni