Re: inserting a column into mySQL DB with perl

There's no real reason to use ids. There once may have been a reason, but certainly not in the case of small text. They just add a lot of useless lookups that could better be served by using the text itself, and confusing people who will just end up remembering a bunch of numbers. Instead, consider Car_Type(Name) and Car(Name, Size).

Though, depending on what it is for, it might be better to add make, model, and possibly year, just in case. In which case, a code would work beautifully: Car_Type(Name), Car(Code, Make, Model, Year, Size).

Comment on Re: inserting a column into mySQL DB with perl

Replies are listed 'Best First'.
Re^2: inserting a column into mySQL DB with perl by diyaz (Beadle) on Jan 22, 2016 at 22:11 UTC
ah i see, yea i just read a blog post on using composite keys instead of ids. However if you are were to make a foreign key you would have to duplicate all those columns? I am assuming when you say code that is like the VIN number of a car that uniquely identifies a car?	[reply]
Re^3: inserting a column into mySQL DB with perl by chacham (Prior) on Jan 25, 2016 at 15:37 UTC
Yes, all columns of a composite primary key must be duplicated in child tables. While they do have their place, common practice is to use a surrogate key instead. This isn't necessarily good or bad practice, it's just what people do. Regardless, the suggestion wasn't for a composite key. When applications track individual vehicles, the VIN is (one of) the best ids, because it uniquely identifies this instantiation of a vehicle. Everything else is merely an attribute, and thus should not be part of the key. If the application is classifying types of cars, however, VIN would be a terrible key, as it identifies individual cars, and not a cars type. For that, it begins to matter if the make or model is an important factor. If it is, it would be a good candidate for (begin part of) the primary key, otherwise, it is just an attribute.	[reply]