creating relational tables in mysql

Question

An attempt to explain this as best as possible:

To begin, I have a table called vocabulary:

+-----+---------------------+
| id  |  name               |
+-----+---------------------+
|  1  |  Farming            |
|  2  |  Agriculture        |
|  3  |  Design             |
|  4  |  Graphic Design     |
|  5  |  Interactive Design |
|  6  |  Animal Husbandry   |
| ... |          ...        |
| 887 |  Carpentry          |
+-----+---------------------+

In an excel sheet, I have lists of 'related vocabulary items' which might look like:

Interactive Design                       Farming
--------------------                     --------------------
Graphic Design                           Agriculture
Design                                   Animal Husbandry

What I want to do is create a table called vocabulary_relations and create relationships between the header (in the above excel sheet example) to every item below it. For example, Interactive Design would point to Graphic Design and Design. Farming would point to Agriculture and Animal Husbandry.

For example, the table would look like this (using the vocabulary table as reference):

+----------------+--------------+
| vocabulary_id  |  related_id  |
+----------------+--------------+
|       5        |       4      |
|       5        |       2      |
|       1        |       2      |
|       1        |       6      |
+----------------+--------------+

That way, I can pull all the related_id for any vocabulary_id.

For example, if I requested vocabulary_id = 1 (farming) it would return related_id => 2, 6 (agriculture, animal husbandry, respectively).

Does anyone have any ideas on how to automate something like this? I am NOT looking for a user interface, this is static data.

Thank you!

When you say "automate"....what exactly do you mean? From what I've read, what you want can be executed by a simple SQL JOIN (assuming that you have all of your tables set up in SQL for a many-to-many relationship; you have shown two of the three required tables). Are you looking for something that automates the updating of said tables? — , Jul 28 '11 at 15:23
I'm not looking to create a many-to-many or anything functional on that level. The data is static and will never change once initial submit. So, I have no problem just using a basic join to achieve what I'm looking for. However, my problem is getting the relationship in the database. What Joe (below) suggested looks promising but not sure I can use that with a CSV. I simply want SQL to analyze a CSV list, find the ID's for each, and create the said relationship in the `vocabulary_relations` table. — stewart715, Jul 28 '11 at 15:30
From the sound of it, you're going to be *far* better off if you manage to pivot your excel data first, then export it. If you're going to give a RDBMS data, it generally expects relational data - otherwise, how is it going to know which entry is the 'parent' entry. (oh, and I think you meant to have 5 relate to 4 and 3, not 2, correct?) — Clockwork-Muse, Jul 28 '11 at 17:16

score 2 · Accepted Answer · answered Jul 28 '11 at 15:22

2

INSERT INTO vocabulary_relations
    (vocabulary_id, related_id)
    SELECT v1.id, v2.id
        FROM RelatedItems ri
            INNER JOIN vocabulary v1
                ON ri.col1 = v1.name
            INNER JOIN vocabulary v2
                ON ri.col2 = v2.name

answered Jul 28 '11 at 15:22

Joe Stefanelli

132,803
19
237
235

Thank you. Will this require me to create a new table for each related item? Or is there a way to use this with a CSV list? – stewart715 Jul 28 '11 at 15:25
I'm not understanding your question. This would populate all the relations in a single table, isn't that what you wanted? – Joe Stefanelli Jul 28 '11 at 15:29
I apologize, I'm new to this (which is why I'm asking these types of questions). Where is SQL obtaining the actual relationship, is my question. Like I said, I have an excel sheet with the items in a list (and a table with all items (unique, no duplicates) along with IDs). So I need SQL to go through each list (roughly 10 to 15 items each) and find the IDs in the current vocabulary table by matching the name and then creating the relationship as demonstrated. Does that make sense? Thanks for your help, Joe. – stewart715 Jul 28 '11 at 15:32
You need to import the Excel sheet into your database. In my query, the `RelatedItems` table is assumed to be that imported data. See: [Import Excel Data into MySQL in 5 Easy Steps](http://blog.tjitjing.com/index.php/2008/02/import-excel-data-into-mysql-in-5-easy.html) for help on the import. – Joe Stefanelli Jul 28 '11 at 15:35
My Excel sheet is called 'related items' and it basically is just a bunch of lists with related items beneath them (items do not appear more than once on the page, once they are in a list thats the category they belong to) – stewart715 Jul 28 '11 at 15:36
If I imported the entire excel sheet into the database table `RelatedItems` it would import every single vocabulary without any record of relationships. Are you saying to import each individual list (10 to 15 related items) into `RelatedItems` run the query, clear the `RelatedItems` table and repeat? – stewart715 Jul 28 '11 at 15:38
I think I've misinterpreted the sample data you presented. I assumed that you were showing two columns from your spreadsheet and that, for example, `Interactive Design` should be related to `Farming` as they both appear on the same row. Can you edit/clarify your sample data to better reflect your case? – Joe Stefanelli Jul 28 '11 at 15:46
Ah no I am sorry, they are VERTICAL lists. The sample data is representative of the excel sheet. So each COLUMN contains related data (the one below is related to the one below and so on). Now that I think about it -- this might change your query a bit but, we need to create relationships for each column header to the items below it. For example, in the data I provided Interactive Design should point to Graphic Design and Digital Design (and not combinations). Headers to subsets only. Is this possible? – stewart715 Jul 28 '11 at 15:56
I've updated my question for clarification. Thank you for your help! Wish I was this smart. – stewart715 Jul 28 '11 at 16:03

score 0 · Answer 2 · answered Jul 28 '11 at 16:18

Did you consider something like this? (Assumes at least a unique constraint on vocabulary.name.)

create table vocabulary_relations (
  vocabulary_category varchar(35) not null,  -- references vocabulary (name), 
  related_vocabulary varchar(35) not null,   -- references vocabulary (name), 
  primary key (vocabulary_category, related_vocabulary)
);

insert into vocabulary_relations values
('Interactive Design','Graphic Design'),
('Interactive Design','Design'),
('Farming','Agriculture'),
('Farming','Animal Husbandry');

Having done that, selecting is dead simple.

select * 
from vocabulary_relations
order by vocabulary_category, related_vocabulary;

vocabulary_category    related_vocabulary
--
Farming                Agriculture
Farming                Animal Husbandry
Interactive Design     Design
Interactive Design     Graphic Design

No joins. Easy to understand and troubleshoot. Dead simple to import from CSV.

This makes sense, unfortunately we're not displaying the related vocabularies. We need to find all related vocabularies and then query certain listings from each (which makes using numbers much mor efficient). Thanks for your help though. — stewart715, Jul 28 '11 at 16:31

creating relational tables in mysql

2 Answers2

Linked