in reply to inserting a column into mySQL DB with perl

...something similar to VLOOKUP in mySQL
Yes there is .. it is called a SELECT statement.

If you already have a CSV file that has idCars, and idSize, the simplest way to incorporate that with existing Cars and Size tables is to create a NEW "relationship" table that has those 2 values.

mySQL supports "join" and views that will allow for easy combinations.

There is no simple/single answer as to what schema changes to recommend - it depends on your environment, and appetite for "n'th normal form". Do you have other programs depending on existing schema consistency ?

If there are only 300 records, it is fine to read either the entire file, or the whole table into memory, and manipulate there.

        "I can cast out either one of your demons, but not both of them." -- the XORcist

  • 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 20, 2016 at 22:26 UTC
    I don't have a solid grasp on the concepts of normalization, and particularly creating database relationships. So I guess I inadvertently created a good example to learn from because I have been "normalizing" by coding repetitive columns into a separate table such as table "Size". However, it seems I am incorrectly trying to create a relationship by inserting it back into the "Cars" table, when I should be joining the two tables as Skeeve suggested above?

    I think my lack of experience prevents me from understanding the implications of these schema design decisions.

    So if I add that third table with idCars and idSize, then I don't need to worry about adding a column to "Cars" table as the relationship now exists in that new table, "CarSize", which I can call upon to join with "Cars" or "Size" table to see the actual names?
[reply]
      Your last statement indicates that you are getting the right idea.

      Typically, this third table is used to crate many-to-many relationships, and you usually want to add more "relationship" data into this table, such as the date when the relationship was established, and perhaps WHO did it.

      In your case, it seems like a particular car can have only ONE size (one-to-one from the car's perspective) , so it makes sense to add the sizeid into the cars table (as a foreign key).

      Now, if you introduce a MANUFACTURERS table with "Toyota", "Honda", "Ford" etc, you will need to add a manuf-id into the cars table (another one-to-one from the cars point of view). Now you can do a somewhat more complex query, to determine what sizes a particular manufacturer makes.

      Have fun !

              "I can cast out either one of your demons, but not both of them." -- the XORcist

[reply]
        actually if you don't mind, I was wondering if the third table was actually a list of unique cars (so no repeats) then it would be a one to many relationship. I think I understand I would define a new relationship table for many-to-many relationship, but if it's one to many relationship and "size" is an attribute of "cars" then maybe "idSize" should be inserted back into table, "Cars"? Sorry if that is confusing.
[reply]
        ah thank you! I think I'm getting teh hang of it!
[reply]

In Section Seekers of Perl Wisdom