Introduction

Ovitec uses a standard database model called relational. A relational database system contains one or more objects called tables. The data or information for the database is stored in these tables. Tables are uniquely identified by their names and are comprised of columns and rows. Columns contain the column name, or variable, data type, and any other attributes for the column. Rows contain the records, or data, for the columns. Here is a sample table called "Customers".

The columns are the variable names, in this example Name, Surname, Address, City and phone. The rows contain the data for each variable in this table:

Primary tables

Female table

Each record, or row, is a female. It contains the variables that are permanent to the life of the animal, such as genetics, birthdate, origin, entry date and other variables in the Current Status sheet of the female file.  It contains the variables related to the current parity of the female (or her last parity if she has been removed).

Parity table

Each record, or row, is a parity. The data of each record refers to a specific parity and contains the variables related to the birth and weaning of Offsprings.

Service table

Each record, or row, is a service. The data of each record refers to a specific service.

PWDeath table

Each record is a preweaned death event.

Treatment table

Each record is a treatment event.

Mating table

Each record is a mating event.

Milktest table

Each record is a milk test.

Lactation table

Each record is a lactation.

Performance table

Each row is a period of time defined by the user. This table includes all statistical data incurred in the breeding herd.

Complex tables

Primary tables are joined to build more complex tables, by combining variables from multiple tables:

FemaleEx Table

FemaleEx table joins the columns of the Female+Parity+Service tables. Each record is a female and includes variables related to the current parity, the last service in the parity, and the last treatment.

ParityEx Table

ParityEx table joins the columns of the Female+Parity+Service tables. Each record is a parity, and includes the female data, the birthing and weaning variables, and the service variables related to the last service in the parity. Important: The record begins with a birthing event. Service related variables in this table are of the same parity as the birthing and refer to the service after the weaning event.

ServiceEx Table

ServiceEx table joins the columns of the Female+Parity+Service tables. Each record is a service, and includes the female, the parity, and all services a single parity.

ServiceResult Table

ServiceResult table joins the the Female+Parity+Service tables. Each record is a service, and includes the female, all services in a single parity and the next parity birthing variables that may result from the last service. Note that in a ServiceResult that results in a birthing, the birthing occurs in the next parity, so if you need birthing information pertaining to a service result use ServiceResult table. The record begins with a service event and ends with the weaning event in the next parity.

PwDeathEx Table

PwDeathEx table joins the columns of the Female+Parity+PwDeath tables. Each record is a pre-weaned death event, and includes the female and the parity table information.

TreatmentEx Table

TreatmentEx table joins the columns of the Female+Parity+Treatment tables. Each record is a treatment, and includes the female and the parity table information.

For example, if you need the information about one female (ID, genetics, ...) to include all her parities (lambing date, liveborn, ...) you need to use the ParityEx table instead of FemaleEx table.

A female record from the Female table can have several parity records in the Parity table, and in turn a Parity record can have several service records in the Service table.

For example, the following SQL sentence:

SELECT Id, Location, Parity FROM ParityEx

This sentence selects a data set where each row is a single parity and there are 3 columns: Id, Location and Parity. It will show:

Location variable shows the current location because this field belongs to the Female table. Female table always refers to the current parity or status of the female. In the above example, the Location value is always 235 for the ID 45 because in all cases, it refers to the current value.

Now, we want to see all service dates. In this case Service table will be used.

SELECT Id, Location, Parity, ServDate FROM ServiceEx

Each row is a service

Note that.Parity variable is valued at 1 for the first 2 services: 9Jan03 and 21Jan03. These 2 services occurred in parity 1. Parity 2 includes only one service on 15Jun03, and finally parity 3 includes 2 services, 30Nov and 15Dec.