[RESOLVED] Database layout

SirEvenhand

I am working an a project and would like to know the best way to set up the database to cover all the details.

1st) all accounts need to be able to be connected. (example member 112 is associated to members #132, #125 and #123 but member #123 is not connected to member #112 on their side)

Set table to have 2 fields - member and associate
112 - 132
112 - 125
112 - 123
132 - 112
125 - 112

2nd) members can pass data items between each member where connection is established (#132 transfers item to #112)

Set table to have 5 fields - giver, receiver, data type, date sent, claimed

As I show above the list can get quite large and I am wanting to make sure that I can store everything without overloading the database while I am on a shared server. If the project takes off I will of course move the project to a larger farm of servers but in the mean time I dont want to have any problems.

Ashley_Sheridan

Use two tables, one for the members, the other for associations. That way, you can have these one-way associations, and it'll be easy to pull out all connected members with a simple join query.

johanafm

And a third table for giving things to other members. That way, you can remove the connection between two members, while still having data about what was sent between them. It also allows people to "be connected" without actually having sent anything to each other.

Given proper indices querying the table should be no problem even with a few million rows, unless you try to retrieve all data (no where clause) of course.

SirEvenhand

johanafm;10936123 wrote:
Given proper indices querying the table should be no problem even with a few million rows, unless you try to retrieve all data (no where clause) of course.

And just how many rows or records can a MySQL database store in a table?

johanafm

That depends on how much physical memory you have, the settings in MySQL's config file and how much data each row contains.

SirEvenhand

Thanks for the replies.

I think I can mark this post resolved unless anyone else has more information.