Basic question: how to properly redesign this schema

Question

I am hopping on a project that sits on top of a Sql Server 2008 DB with what seems like an inefficient schema to me. However, I'm not an expert at anything SQL, so I am seeking for guidance.

In general, the schema has tables like this:

ID | A | B

• ID is a unique identifier
• A contains text, such as animal names. There's very little variety; maybe 3-4 different values in thousands of rows. This could vary with time, but still a small set.
• B is one of two options, but stored as text. The set is finite.

My questions are as follows:

• Should I create another table for names contained in A, with an ID and a value, and set the ID as the primary key? Or should I just put an index on that column in my table? Right now, to get a list of A's, it does "select distinct(a) from table" which seems inefficient to me.
• The table has a multitude of columns for properties of A. It could be like: Color, Age, Weight, etc. I would think that this is better suited in a separate table with: ID, AnimalID, Property, Value. Each property is unique to the animal, so I'm not sure how this schema could enforce this (the current schema implies this as it's a column, so you can only have one value for each property).

Right now the DB is easily readable by a human, but its size is growing fast and I feel like the design is inefficient. There currently is not index at all anywhere. As I said I'm not a pro, but will read more on the subject. The goal is to have a fast system. Thanks for your advice!

Answer 1

This sounds like a database that might represent a veterinary clinic.

If the table you describe represents the various patients (animals) that come to the clinic, then having properties specific to them are probably best on the primary table. But, as you say column "A" contains a species name, it might be worthwhile to link that to a secondary table to save on the redundancy of storing those names:

For example:

Patients
--------
ID  Name   SpeciesID   Color         DOB         Weight
1   Spot   1           Black/White   2008-01-01  20

Species
-------
ID   Species
1    Cocker Spaniel

If your main table should be instead grouped by customer or owner, then you may want to add an Animals table and link it:

Customers
---------
ID   Name
1    John Q. Sample

Animals
-------
ID   CustomerID   SpeciesID   Name    Color        DOB          Weight
1    1            1           Spot    Black/White  2008-01-01   20

...

As for your original column B, consider converting it to a boolean (BIT) if you only need to store two states. Barring that, consider CHAR to store a fixed number of characters.

Answer 2

Like most things, it depends.

By having the animal names directly in the table, it makes your reporting queries more efficient by removing the need for many joins.

Going with something like 3rd normal form (having an ID/Name table for the animals) makes you database smaller, but requires more joins for reporting.

Either way, make sure to add some indexes.