Look up Table and use of Primary Key

Question

Hi, I have a question regarding DataBase design.

I have a Entity Table named: CARS and I have a Lookup Table named: COLORS

Table CARS has: Id, Name and Table COLORS has just: ColorName

Table COLORS has a short list of colors, instead CARS could have a long list of entities.

CARS has a Primary Key on ID.

My questions are:

• Adding a Primary Key on Lookup Table COLORS on ColorName, could improve performance?

Table CARS will have a CLUSTERED INDEX on ID.

• Would you make sense have a CLUSTERED INDEX on CARS on ColorName Thanks gus for your time!

Answer 1

That's a bit odd. You'd want the COLORS table to have ColorID and ColorName, with the clustered index on ColorID and a foreign key to it on the CARS table. If I'm understanding correctly.

Answer 2

1 - If COLORS is a short table, it may improve performance but probably won't really be noticeable.

2 - You can only have one clustered index, and it makes more sense from the limited info you gave for it to be on ID. You will have duplicate entries in ColorName.

Why don't you change colorname to ColorId, add an ID column to Colors and use that as a clustered index/primary key?

Answer 3

Clustered indexes can increase the speed of retrieval or inserts, but only when the data is retrieved or updated/inserted in the order that matches the index order.

Otherwise it can decrease performance.

For more detailed answer the question needs to be more specific.