Unique ID Suggestions?

[vivi288]

Hey guys, I'm trying to normalize a database for a friend, who has a car database. I'm trying to separate the database by having one table for the car brands (BMW, Honda, etc), one table for the car types (SUV, car, sports car, etc), and the final table for the rest of the info (such as year/model/price).

However I'm stuck at this one tiny detail: The way he currently has his tables set up has each car with a unique ID. (For example, in the honda table, the ID 11 entry is the Honda Accord 2010.) He wants to keep Honda Accord 2010 as the 11th entry under the brand HONDA (doesn't matter if it's the 11th entry overall). If the 11th entry under Toyota is the 2009 Toyota Camry, then the 11th entry under TOYOTA should be the Camry, regardless of whether its overall ID in the new car database is 2983 or some other random increment.

My normalization would eliminate that unique ID, as far as I'm concerned. What would you suggest? Is there a way to work around this that I am unaware of?

Note: I know how to pull the information out of the database, as I would just condition it to select by brand and the car ID, but what I don't know is how to make the database AUTOINCREMENT these values upon entering info to the database.

[jlego]

leave those ids there and start incrementing after the existing data?

[vivi288]

Hi jlego,
thank you for your response.

I'm not quite sure what you're recommending I do?
If I'm understanding you properly, what ends up happening is it just increments after the last result, which I don't want.

For example assuming a Nissan Murano is the 1st entry under Nissan and the Honda Accord is the 1st entry in Honda, if I add this info:
ID = 1, carBrandID = 2, model = Murano, year = 2010, carID = (autoincrement)
ID = 2, carBrandID = 1, model = Accord, year = 2009, carID = (autoincrement)

I get:
carID = 1
carID = 2

When I want:
carID = 1 (1st entry in Nissan)
carID = 1 (1st entry in Honda)

(assuming that I only have these two entries in the database)

[pritaeas]

Sounds a lot like that column is just your sort-order. You may want to write a trigger on insert, that checks how many are present for that brand and adds one.

[smantscheff]

To be more precise, the trigger should not check how many but the maximum id value for cars of this brand and then add one.