CODEWITHSUNDEEP
databasesqlite
Kenta1561
Kenta1561

Reputation: 29

Store details of a row in an SQLite DB

I would like to know how I can store details of a row efficiently in an SQLite DB. For example:

Main-Table:
ID | NAME | CITY
1 | Peter | Paris
2 | Sarah | New York
3 | Seth | Chicago

I want to store details about a person in the database but I don't know how I should do that. The problem is that the data could be represented as an Array in a program code but I can't do that in a DB. My though was this one:

table "person_1": (table for storing favorite places in the city)
NAME | ADDRESS
Eiffel Tower | Champ de Mars, 5 Avenue Anatole
Musee du Louvre | Musée du Louvre, 75058 Paris

table "person_2": (same purpose)
NAME | ADDRESS
One World Trade Center | 285 Fulton Street
Statue of Liberty | Liberty Island

And so on with other persons...

Is this the right approach or can I do it more efficiently?

Upvotes: 1

Views: 48

Answers (2)

Georg Schölly
Georg Schölly

Reputation: 126095

This is solved by having 2 tables:

Table People:

id | name  | city
 1 | Peter | Paris
 2 | Sarah | New York

Table Favorite Places:

id | person_id | name                   | address
1  | 1         | Eiffel Tower           | Champ de Mars, 5 Avenue Anatole
2  | 1         | Musee du Louvre        | Musée du Louvre, 75058 Paris
3  | 2         | One World Trade Center | 285 Fulton Street

This way you can easily select all favorite places belonging to each person.

You can even write queries JOINing two tables:

SELECT p.name, f.name, f.address
FROM people    AS p
JOIN favorites AS f ON p.id = f.person_id

and you'll get:

Peter | Eiffel Tower           | Champ de Mars, 5 Avenue Anatole
Peter | Musee du Louvre        | Musée du Louvre, 75058 Paris
Sarah | One World Trade Center | 285 Fulton Street

Upvotes: 1

erenbasaran
erenbasaran

Reputation: 21

Main-Table which stored peoples like;

id | name  | address
1  | Peter | Paris
2  | Sarah | New York

Favorite_Place table for places like;

id | name            | address
1  | Eiffel Tower    | Champ de Mars, 5 Avenue Anatole
2  | Musee du Louvre | Musée du Louvre, 75058 Paris

and the middle table something like;

person_id | place_id
1         | 1
1         | 2
2         | 1
2         | 2

If you design tables something like that, you aren't store favorite places again and again in favorite_place table.

Upvotes: 2

Related Questions