I want to make a feature table but not quite sure how to design my table.

As a hypothetical example imaging a group of cars.
Audi
BMC
Chevrolet
Datsun
Envoy
Ford
GM
..
..
..
VW


And imagine a list of colors

Black
Cyan
Green
Magenta
Orange


So I want to make a two dimensional table where where the rows are the type of car and the columns are the colors.

-------------------------------------------
Audi | Y | N | Y | N | N
BMC | Y | N | Y | N | Y
Chevrolet | Y | N | Y | N | Y
Datsun | Y | N | Y | N | Y
Envoy | N | N | Y | N | Y
Ford | Y | N | N | N | N
GM | N | Y | Y | N | Y
Hyundai | Y | Y | Y | N | N
VW | Y | Y | Y | Y | Y

Y means the car is available in the color N means it's not.
I have a table that has a list of Car Names and a Table that is a list of colors.
Now what is the structure of the table that is the 'feature' table?

On 12/9/2011 11:09 PM, SpreadTooThin wrote:
And what happens when you want the color blue? And later, silver? Then
red?

Read up on database normalization. Wikipedia has a reasonable start at it.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On Friday, December 9, 2011 11:09:37 PM UTC-5, SpreadTooThin wrote:
Off the top of my head, I would probably make the table with 2 columns: car_type, and color. Set each to be a varchar(20) (assuming that you don't have a car or color more than 20 characters in length).

Then, have a bunch of rows, like:

Audi | black
Audi | green
BMW | black

And so on.

This way, in whatever program you're using, you would SELECT color FROM table WHERE car_type='Audi' (or whatever), then load the result into an array.

On Friday, December 9, 2011 11:55:29 PM UTC-5, Jason C wrote:
I guess I should add that, instead of making them VARCHARs, you could make them an ENUM field, and specify the options in the beginning.

MySQL states that this is faster to query, but I just made that modification to a table with 4 million rows, and the speed increase was by 0.001 seconds. I changed it back so that it would be easier to add new options in the long run.

SpreadTooThin wrote:

one has car makes
one has colors

one has two links - to the car, and to the color.

So you can establish an entity in the links table for an entity that is
a member of tow entirely different sets - car makes, and car colors.

On 12/10/2011 12:28 AM, Jason C wrote:
It's one way, but not necessarily the best way. What happens when
someone enters "blakc"? Typos do happen.

And using an enum is even worse. Not only does MySQL not check the
values entered (unless running in strict mode), adding a new color
requires an ALTER TABLE.

Performance is NOT a main criteria!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2011-12-10 06:28, Jason C wrote:
Be careful with enum's in the datalayer, by definition they behave
different than other datatypes. As an example, a foreign key on an enum
column will refer to the position in the enum, not the value.


/Lennart