0

I've tried to solve this issue myself but I can't seem to find a work around as I'm pretty new to SQLite.

Basically I've got a large db of what people like structured by the likes themselves (with a row for each like and several rows for each person). What I would like to do is restructure it so that each person is a row and the likes are columns in the table (binary variables saying whether person likes item or not).

But the complication is that I would like to do that on an adhoc basis pulling different tables out of the data where the number of columns would change.

If anyone has an answer or guidance that would lead me to one it would be much appreciated.

Thanks

Matt

Matthew Bowles
  • 1

1 Answers1

1

What you are describing is usually called a "many to many" relationship. The recommended way to represent it is to use three tables.

  1. The table of persons
  2. The table of "things" people like
  3. The table of associations between persons and things they like

The third table has two columns: a person ID, and a thing ID.

Since a person can occur many times in the association table, each time with a unique thing, and a thing can appear many times in the association table, each time with a unique person, it is called a many-to-may association.

If you search for "SQLite many to many" you will find lots of ideas of how to create the schema for this structure (with options such as referential integrity, cascading deletion, etc.) and queries for information.

Community
  • 1
  • 1
Doug Currie
  • 40,708
  • 1
  • 95
  • 119