1

I have to create a table to map some strings that are my input, to the result value that is a value that I can choose, like an id (or string). So, to make an example, I have an excel file made of a not normalized list of names, that are all the possible values that I can expect:

  • "audi A4"
  • "Audi A6"
  • "audi a6"
  • "ford focus"
  • "my beautiful car from Henry F."

And manually (luckily there're not so much elements), I have to associate them an ID or an identificative TAG that I can choose, for example I'm creating a column TAG and chose as values "AUDI" and "FORD":

description tag
audi A4 AUDI
Audi A6 AUDI
audi A6 AUDI
ford focus FORD
my beautiful car from Henry F. FORD

There's no way to automatically discover the tag, so I have to do it one by one. My question is if the column TAG is ok or if it's better to put in the tag column an integer as foreign key to another table that will be mapped as:

id tag_description
1 AUDI
2 FORD

Do I really need this associative table?

I was thinking to uppercase all strings values of description, so I will not have both "Audi A6" and "audi a6", and after from my Java code will be easy uppercase the input and check it with the column description, do you have others suggests?

2
  • No I will not need in future to store other vendor infos. I just need the vendor id to process a description and assign it an unique vendor id value. Jul 14, 2022 at 14:16
  • Having worked for a motor vehicle department, and working on migrating some code + data, we found an amazing number of misspellings of car manufacturer names: transposed letters, adjacent key presses and just plain bad spelling. There were tens of thousands of misspelled mfgr names over a 40 year period of clerks entering data (Chevrolet, for example, had 126 different spellings) of cars, motorcycles, trucks and boats.
    – Tangurena
    Jul 18, 2022 at 20:48

1 Answer 1

4

Your examples indicate the tag is nothing but a vendor id (or brand name), so better call the extra column VendorId, not just Tag.

Now it should become apparent your question is essentially "Do I need a separate table for all vendors?". That is something you need to answer for yourself: in case you need to store or display more vendor information than the vendor id, a second table would be useful. Otherwise, it would probably be overdesign.

Another point is the datatype (integer vs string). In case the vendor or brand can change afterwards, a separate table with an integer foreign key will allow mutation of vendor names more easily. Otherwise I would stick to the simple "one table" approach.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.