Associated databases, good or bad?

[nburlington]

I need some advice on planning a database structure.

Is it a bad idea to break up your data into different databases? For example, if I am collecting:

username
password
firstname
lastname
address
etc

and then
last movie rented
favorite movie

...would there be any reason to break out the movie data from the user info data?

The reason I ask is that my hosting plan has a 100mb space limit. I'm sure i'm being too cautious about hitting the ceiling but I'd like to have a plan just in case.

If you had multiple databases with associated data, are you incurring a larger delay time to query the extra db? Just curious.

[hl]

I certainly hope you didn't pay for that hosting plan. At the same time, don't underestimate what you can hold in 100MB.

You'll experience extended latency with each query, given that you have to connect to a remote location.

[Esherido]

Using simple plaintext using commas and newlines (Effectively just CSV.) you could likely fit at least 10,000 records, probably even more, within 100 MB. And like hl said, I hope you're not paying for 100 MB of space.

[Templarian]

I think hes just underestimating what 100mb holds :lol:

Your using simple text data, if you feel your going to fill it, simply use sqlite. That way you basically can't run out.

[nburlington]

I meant I had a 100Mb limit on any single mysql database, not for the entire site. Anyone know of a host with better specs let me know

[hl]

Just (http://dreamhost.com) about (http://mediatemple.com) all (http://godaddy.com) of (http://joyent.com) them. (http://slicehost.com)

And, that's just the first few that come to mind.

edit:/ I'm going to guess you're on 1and1.
edit2:/ Since they're on the same host, they're likely within the same internal network. That just means you probably won't have any more latency than you usually do (you don't have a problem).

[Templarian]

^Yea, I'm on 1and1 and I think its the only host that does have the 100mb limit, but really if your filling 100mb databases you shouldn't probably be with 1and1 :lol:

really if you have the time look at sqlite its really easy, and has 4 data types

[darroosh]

really if you have the time look at sqlite its really easy, and has 4 data types
Please Templarian , Can u tell us what is the reason to look for sqlite instead of mysql , i mean its advantages over Mysql , and also its disadvantaes

[nburlington]

I don't think I'm going to hit the 100mb limit (yes I'm with 1and1). It just made me wonder how data is stored for larger datasets.

For example, if I go onto netflix and give user reviews for 10,000 movies. Is my username and rating associated with that movie or is that movie and its rating associated with my user info? I feel like there's some association going on and I'm wondering if its a common practice or if there's a more elegant solution when you want to do something similar.

[nburlington]

...and what about forums? I imagine a forum with a decent amount of members could hit 100mb of db storage pretty quickly no?

[hl]

For perspective, 100 MiB is 100 * 1024KiB, 102400KiB is 100 * 1024 * 1024 bytes, 104857600 bytes is 104857600 ASCII characters. Tell me you forecast approximately 100 million characters of raw data, and I call you ambitious. Obviously, it's very possible to fill those 100MB, and surpass it as such... the ceiling's pretty high though (for a small site). :P

There's a nice tutorial on Relational Databasing by Brian Haveri over on the main site, take a look at it. Basically, the user owns the review, so the review possesses an ID that is a foreign key linking to a user's primary key. If you gave each user their reviews, then you'd have some random huge list in the users table, which would make no sense. Essentially though, everything should be related in some way.

[Templarian]

Please Templarian , Can u tell us what is the reason to look for sqlite instead of mysql , i mean its advantages over Mysql , and also its disadvantaes
Pros:
Free
Based on a flat file so transferring a site based on sqlite db is very simple.
4 data types (could be a con, but makes it easy)
There is a library for every language (that usually matches the language's native mysql or postgresql library/class.

Cons:
Needs to be vacuumed once in a while as it can grow in size. You just have to call "Vacuum" in the sql.
Needs PHP5 (*which most have)
You have to backup yourself as your host will not do this unless they back up files.

[mlk]

why not fill databases as you go and add another as soon as you hit the limit ?