Limit, Structure and Scalability question

[Phases]

I've found some documentation but I'm having a hard time wrapping my brain around some of it, so hopefully you guys don't mind if I just ask straight forward here, and hope for some help.

 

1. Is there a limit I should be concerned about as far as how many rows in a table, or how many rows in a DB period. My concern, with what I'm planning, is that I may have 100's of millions of rows or more at one point. Imagine the scenario:

 

- 100,000 users. Each user has 500 items, and each item has 10 keywords associated with it that I want to track, some will be the same, some will be different.

- Supposing it is best to keep each record  in a single table. So, rows might look like:

 

username1 | Item1 | keyword1

username1 | Item2 | keyword2

username1 | Item3 | keyword1

username1 | Item4 | keyword3

username2 | Item5 | keyword4

username2 | Item6 | keyword5

username2 | Item7 | keyword2

username2 | Item8 | keyword6

 

.. Suppose 100,000 usernames, all posting up to 300 items each, with up to 10 keywords each. That's a potential 300million rows - with (if this project was a success) a potential of 10x that.

 

2.Would it be better to keep all those records in one table, or try to break to multiple?

 

Very often: Display a users items to them with all the keywords associated. Allow them to quickly and easily change keywords associated with their items and which items.

Somewhat often: Let a user search ALL keywords and return the item(s) associated with them, while comparing to their own item's keywords.

 

The problem I'm having trouble with is.. image them wanting to list their keywords and what items they are associated with. It would be much easier to query one table and return all results with that keyworld && username, rather than search 40 databases (if i were breaking keywords into tables by first letter or number/symbol for example.  Imagine the nighttmare of changing keywords around various items. Then toss in searching for other Item's out there that share the same keywords, from other users. Seems considerably easier to do from one table.

 

3. Scalability.

 

If I get full to burst, due to whatever limitation (server, mysql) and need to expland - is this a concern? Can I.. you know, share tables or databases so to speak, so I can basically have a cluster or use a couple as if they were one?

 

Just a simple "yes people do that all the time, worry about that in a couple years", or "that's hard and requires proper planning up front". will be fine.. No need to really elaborate if you don't want.

 

Hope all this makes sense, any insight appreciated.

Thanks!

 

[Phases]

Also:

 

MySQL server version : 5.0.51a-3ubuntu5.4-log

 

[fenway]

Have you started this project yet? If not, these three shouldn't be your primary concern.

[Phases]

I have not. I'm mid-researching MyISAM vs InnoDB - if I need to worry about foreign keys, backup/restore/move differences, etc. I'm still mapping out tables in a spreadsheet.  Also, How to handle combining five of these projects (that are more like mini projects) that will effectively all be the combined into one of a much larger scale. if it'd be best to give them each their own database, or even their own mysql instance period.

 

How common is it to be on a website that connects to several different databases all from  the same webpage.

 

This is all pretty new to me. I've done a few things with php/sql but never anything put into production, and long ago. Arguably, something of this size isn't something someone of my experience level (that of very little) should even attempt. As you've probably figured out.

 

But hey, where do you get if you don't grab the bull by the horns? I'm a pretty fast learner and am willing to bring on a partner who basically has the same skill set as me, willingness to learn, and ambition. At the very least I'll learn some stuff I can apply later.

[Phases]

Really hoping for some helpful insight from someone.  I'm a little surprised I've got no help. Anyway, I guess I'm not so concerned with the OP questions as I am the following:

 

Imagine five web sites. They all share the same USER information. Do I want to:

 

- have five separate databases databases and every time user tables are changed (get a new user, username changes, etc) , i update all five of them at once.

- have six separate databases, one for each site and then a single user DB that they all share, and try to pass user_id through sessions so I only have to reference the six DB on login/out and UPDATE

- or, have all five sites in a single DB with table prefixes.

 

..and why?

 

Or does it really matter?

 

I'm trying to learn and have been googling for a while now, reading about thread locking and everything else - so really looking for some help understanding what's best.

 

[Phases]

Really hoping for some helpful insight from someone.  I'm a little surprised I've got no help. Anyway, I guess I'm not so concerned with the OP questions as I am the following:

 

Imagine five web sites. They all share the same USER information. Do I want to:

 

- have five separate databases databases and every time user tables are changed (get a new user, username changes, etc) , i update all five of them at once.

- have six separate databases, one for each site and then a single user DB that they all share, and try to pass user_id through sessions so I only have to reference the six DB on login/out and UPDATE

- or, have all five sites in a single DB with table prefixes.

 

..and why?

 

Or does it really matter?

 

I'm trying to learn and have been googling for a while now, reading about thread locking and everything else - so really looking for some help understanding what's best.

 

 

Further, there would be other aspects of the whole thing that would need to be shared in the one database. PM system, maybe something like reporting of users, paid services, notifications, etc.

 

Part of the reason I need this mapped out well at the beginning is to ensure I can break the sites apart easily later if I want to.

 

So, would it be best to have each site on its own DB but sharing a single seperate userDB, or have each site all have their own user tables and update all every time as needed (seems like too much), or have it all in one big database with prefixes to distinguish the sites.

 

In regards to #3,

 

1. Is exporting a db based on prefixes easy?

 

Say I have a db with tables:

 

users_user

users_userprofile

users_PM_stuff

users_other_shared_stuff

site1_table1

site1_table2

site2_table1

site2_table2

site3_table1

site3_table2

 

...if I wanted or needed to down the line, would it be easy to export (users_* AND site2_*) for importing into a new db for just that site?

 

2. Suppose the five sites on 1 db caused the db to hit it's limit one day. I assume it's easy to cluster/scale?

 

Any insight appreciated..

 

[fenway]

I meant in terms of scalability... this need not concern you at this stage, you'll just get lost in subjective opinions -- every case is different.

 

Go with InnoDB, and don't use separate databases if you want to share anything.

 

If anything, you can store the users in their own table, and use project prefixes -- or, if you want, separate project DBs and a users DB.

[Phases]

Thanks for the reply! And I'm sorry if I repeat myself here but, I just want to be comfortable before I begin. This issue is one of the two problems bugging me, and I'd like to begin tonight. So:

 

Yeah, i'm debating prefixes vs separate project db's and a separate users db.

 

The reason I'm concerned is because I suspect a single project to potentially be huge, huge enough for me to be concerned about about scaling. To add the other four right on top of it? Scary. Also, I'm factoring in the possibility that one of them may, for example, be sold one day and need to be pulled out.

 

I guess as long as its easy to export database sections based on prefixes it doesn't really matter. I could all in one db, users prefix and separate site prefixes, and if I need to break a site out I could export its prefix and the users prefix, then import that back into a new database. Similarly, if the DB gets too big (or simply run outa storage) and I want to break each sites database (prefixes) onto its own server, I could just export each prefix set, import on their respective new servers, then just use the one users db (the only prefix left on original db) as shared for all. Am I correct in assuming this is no problem? So, put all in one DB, uses prefixes for each site a a users prefix (like last reply showed) and adjust accordingly later.

 

The other option, 6 DBs, one for each site and one users, but then each site has to connect to two databases for nearly ever query made. Terrible idea right? Or no biggie?

 

Thanks again for the back and forth, I just like being able to talk to someone about it.

 

 

[fenway]

Since each project is functionally distinct, it makes logical sense to keep them distinct at the database level as well -- so 6 DBs.

 

And as long as the mysql users have acccess to both DBs, and they're on the same machine, another connection is not a big deal.

 

And like I said earlier, concerns about scaling are premature.

[Phases]

Cool thanks. I had decided to do all in one DB with prefixes in the meantime, because I was itching to get started. Figured I could break out what I needed later.

 

I've only actually set up about 12 user tables though, so at this point I could easily go either way. Login system is nearly done but that's it.

 

Thanks again!

 

My next concern is how to deal with .. keywords (kinda like tags). But that comes later and I'll maybe have it figured out by then - if not ,I'll ask.