Best way to store an array of numbers in a MySQL DB?

[Waldo 96]

Just repeating what the title says; what is the best way to store an array of numbers in a MySQL DB?

 

For example, I have 2 tables, 'sites' and 'people'.

 

The sites table, may contain any number of (0+) users. I want to store indexes in to the people table, as some kind of array in the sites table.

 

Any suggestions?

 

Thanks,

[Guest]

What are you trying to store? You don't store arrays in a database table.

 

I assume you're saying that 'a single site may contain many people and a single person can belong to many sites' ?

 

If so you need a joining table which has a unique index and a foreign key for each row of peopleId and siteId.

 

Assuming that's what you mean?

 

However if a person can belong to only one site then you need to store the siteId as a foreign key against each person.

[Waldo 96]

Thanks.

 

A person can belong to more than one site...

 

I am storing the following tables:

 

Table 1 - list of websites.

Table 2 - list of people (name, email address, twitter id, etc) who may be connected to one or more websites from Table 1.

 

Someone else has just suggested having a third table to link the above 2 tables.

 

Table 3 - list of connections (i.e. table_1_id, table_2_id).

 

Is that the same thing you're saying?

 

I'm not sure re: the foreign key (don't understand the terminology). Presumably that's just an id to another table in the database, is that correct?

 

Assuming I've understood you correctly, it would also make sense that I would only need 2 tables, should each person only be able to belong to one website.

[LLVL 10]

website: website_id, url, name

person: person_id, name, username, email

person_website: person_id*, website_id*

 

The underline indicates the primary key, the * means foreign key.

 

Basically the person_website table is a joining table, it maps people to websites. You need to look up about relational database design and normalisation

[Guest]

Thanks.

 

A person can belong to more than one site...

 

I am storing the following tables:

 

Table 1 - list of websites.

Table 2 - list of people (name, email address, twitter id, etc) who may be connected to one or more websites from Table 1.

 

Someone else has just suggested having a third table to link the above 2 tables.

 

Table 3 - list of connections (i.e. table_1_id, table_2_id).

 

Is that the same thing you're saying?

 

I'm not sure re: the foreign key (don't understand the terminology). Presumably that's just an id to another table in the database, is that correct?

 

Assuming I've understood you correctly, it would also make sense that I would only need 2 tables, should each person only be able to belong to one website.

 

You need to read up on basic database terminology if you're going to use them. You at least need to read up about 1 to many, many to 1, many to many and 1 to 1 relationships in databases.

 

Yes, you've basically repeated what I said