SQLite Forum

How to relate a row with more than one topic? See example

How to relate a row with more than one topic? See example

(1) By AleC (AlexCR) on 2021-05-31 20:41:25 [source]

Hi there, I'm new here. I want to make more than one association with a row. Example:

'Quotes' table:

id quote 1 A successful man is a fall in love man. 2 Love is happiness. 3 Success consist on hard work.

'Topics' table:

topic idTopic Love 1 Man 2 Success 3

Now, as you can see there are more than one (2 rows) rows with the 'love' topic included. The same for 'success'. How can I made this relation in sqlite?

(2) By Tim Streater (Clothears) on 2021-05-31 21:29:52 in reply to 1 [link] [source]

I'd make another table to link the two together, with two columns: topicid and quoteid. Add a row to this table for each connection.

(3) By Larry Brasfield (larrybr) on 2021-05-31 21:31:36 in reply to 1 [link] [source]

The usual way to model an N-to-M relationship, where N and M can be anything greater than zero, is with a separate link table. For your example, it would have columns that might be named Topic_id and Quote_id , which would both be foreign keys into the Topics and Quotes tables.

(4) By AleC (AlexCR) on 2021-05-31 21:54:04 in reply to 2 [link] [source]

Thank you Tim... that's ok. The problem with that is that the table could be a lot of rows. If I have 1000 quotes and each one contain at least 5 topics, then that table would reach 5000 rows. MYSQL support this with a column format that SQLITE does not support.

(5) By AleC (AlexCR) on 2021-05-31 21:54:45 in reply to 3 [link] [source]

Thank you!

(6) By Tim Streater (Clothears) on 2021-06-01 13:37:54 in reply to 4 [link] [source]

5000 rows in an SQLite table is nothing. And which column format does SQLite not support?

(7) By David Raymond (dvdraymond) on 2021-06-01 15:15:59 in reply to 6 [link] [source]

And which column format does SQLite not support?

Don't know about MYSQL, but in PostgreSQL for example you could have an array field to hold all the topics

create table quotes ( quote_id integer not null primary key, quote text not null, topic_ids integer[] ); insert into quotes values (1, 'A successful man is a fall in love man.', array[1, 2, 3]);

But the only advantage of that would be 1 less table and a lower overall record count, which doesn't really help anything. You couldn't make them official foreign keys, updates would be harder, indexing going from topics to quotes would be harder, etc.

So, as people have recommended, it would be a lot better for the normal

create table quotes_to_topics ( quote_id int not null references quotes, topic_id int not null references topics, primary key (quote_id, topic_id) ) without rowid; create index topics_to_quotes_idx on quotes_to_topics (topic_id, quote_id);