SQLite - SELECT over multiple entries of the same data/column

Question

I am a bit new to SQLite, and I am having a slight dilemma about my database design. I'll explain. Suppose you and your friends use a number of different nicknames on the Internet (can be more than one per person). What we have here is an ID of the person and a list of all nicknames that this person uses. This is a single entry in a large list. The goal here is to store this data by using SQLite in a way the SELECT statement can be used to fetch all entries that contain the specified nickname.

I have thought about creating a table with two columns, first being the ID (primary key) and the other being a TEXT that holds nicknames in a CSV format. However in this example I don't know how to write the select statement to search and match nicknames stored in CSV.

Ideas?

Answer 1

Why not just have the following tables:

Person (columns: person_id, person_name)

Nickname (columns: nickname_id, nickname)

Person_Nickname (columns: person_id, nickname_id)

Then you create foreign keys from Person to Person_Nickname and from Nickname to Person_Nickname. This allows a given person to have as many nicknames as you like.

Then, to find all persons which match a given nickname, you can write:

SELECT p.person_name
  FROM person.p
     , nickname n
     , person_nickname pn
 WHERE n.nickname = 'nickname of interest'
   AND p.person_id = pn.person_id 
   AND n.nickname_id = pn.nickname_id

Answer 2

You need 2 tables:

• users
• nicknames

In table "users" you have id, name, other optional information on the user. In table "nicknames" you have user_id, nickname.

It's a one to many assosiation. Then to obtain a list of all user nicknames you query it like this (using the user id):

SELECT nicknames.nickname FROM nicknames WHERE nicknames.user_id=id

As a less database like implementation you could do this:
use only one table with 2 fields (user, nickname). Then you get a list of all john's nicknames (you could use ids, too) associated with a user with a query like this:

SELECT table.nickname FROM table WHERE table.user="john"

The CSV approach works but you would need to implement your own function to add/remove/parse nicknames, and it would almost certainly be slower that the two implementations I explained.

Answer 3

For starters, here is what you have

SELECT Name, Nickname FROM MyTable WHERE Nickname LIKE "%Copper%";

But I would strongly suggest having a Table for Names, and a table for Nicknames used, such that the Nickname has a reference to Names.

CREATE TABLE Users (
    UserHnd INTEGER PRIMARY KEY, 
    Name TEXT
    );
CREATE TABLE Nicknames (
    NickNameHnd INTEGER PRIMARY KEY, 
    UserHnd INTEGER REFERENCES Users(UserHnd), 
    NickName Text
    );

Then, query it with either an INNER JOIN:

SELECT Users.Name, NickNames.NickName 
    FROM Users INNER JOIN NickNames ON User.NameHnd=NickNames.NameHnd
    WHERE NickNames.NickName = 'Copper';

Or specify the join with a WHERE clause:

SELECT Users.Name, NickNames.NickName 
    FROM Users, NickNames 
    WHERE User.NameHnd=NickNames.NameHnd
    AND NickNames.NickName = 'Copper';

Or a nested query:

SELECT Users.Name
    FROM Users
    WHERE User.NameHnd IN (
    SELECT NickNames.NameHnd 
        FROM NickNames 
        WHERE NickNames.NickName = 'Copper');

You will find this scheme will give you more control to edit entries, remove them, etc.