Database design - two tables

Databases
1

Hi everyone, I don’t have a full vocabulary on this subject, so I apologise if I’m using the wrong words or not making full sense.

Basically, I need to have a database that has two tables - skills and examples. The records in both these tables need to be able to relate to the other table - so, for example, if I have a record in the “skills” table of “building”, I want that record to know which records in the “examples” table are examples of “building”.

I also need the reverse to work, so “example 1” in the “examples” table will need to say which skills in the “skills” table it is an example of. Each record in both tables will correspond to multiple records in the other table, never just one.

The only idea I’ve been able to come up with on my own is having a field that is essentially a string list of ids of the other table, which I’d then use with explode(). I don’t have a great deal of experience in this, but to me it seems like a stupid idea . . . Ideally I’d be using just the one query to do this (which would not be the case with my idea here).

Does anyone have any suggestions as to what is the best way to do this? Please let me know if I need to clarify anything about what I’ve written.

Thanks for any suggestions.

2

It’s a many to many relationship, and that means you’ll need a third table. Storing a string comma separated id’s in a column is a bad idea. It will become very difficult to do the queries you described in your post.

Tables:

skills: id, skill, …
examples: id, description, …
exampleskills: exampleid, skillid

So the third table will contain a row for each example-skill pair.

And to extract what you want, you’ll have to join the tables in your query. Read the manual for more info on joins if you need to: http://dev.mysql.com/doc/refman/5.5/en/join.html

3

See, I could tell my idea was stupid :stuck_out_tongue: