where to start on this project...

[eluser]vecima[/eluser]
Hi there.

I've been around this helpful community before, and I've created my website in CI. I think it's awesome. The thing is, while I like web development, I'm more into game development (which is what I made my site for) so When I finished the site, I went back to that hobby, and let my site stay as is. I lost the mental momentum (so to speak) required to create a feature rich community site.

Now I want to turn it into a community site where people can create profiles, upload their work to share, form groups, and start projects, as well as share their thoughts in the form of articles that everyone can see if they so wish (or if they don't wish, that they can keep private to themselves or to just their group). Also this needs to be secure, so while people can upload more than just images (probably .zip and .rar files) they shouldn't be able to bring the whole thing down.

I have many of the ideas pretty well formulated, but before I started "slinging code" I thought it would be a good idea to attack the database design first. Now this is where I've reached my latest mental block. I have two ideas on how to drive this sucker:

1. Have a table for "items". Everything on the site has an associated item table entry, so the table size for items would be the size of everything else combined. aside from that, the tables that would arise would be: users, articles, news, ratings, maps, models, textures, groups, tutorials, and possibly some others that i'm not thinking of yet. It seems messy, but do-able (and I do have enough patience and attention to detail to stick with a tedious task like getting all of the bits of data to play nice).

1. Have a table for "items". Everything on the site is an item. instead of having a bunch of other tables to hold the different types of content, the "items" table would just have every field it needs so that it is all the pieces of content. This way, the database is easier to manage, the queries are easier to follow and understand, and the code is easier to write. What I don't know is if there is anything lost doing it this way. certainly there would be lots of pieces of data that don't use the entirety of the "item".

Does anyone have any thoughts that might help me out? If what I've asked has been asked a billion times before, then I apologize, but I was unable to think of search terms generic enough to get results and specific enough to answer my question.

thanks in advance,

vecima

[eluser]richthegeek[/eluser]
You are sacrificing efficiency of speed and size for ease of development...

Having every item in one table means that many rows will have lots of unused columns, and for the most part these all take up space for NULL values (often represented as a full bit string rather than simply a NULL string)

Similarly, this individual table will be very large and thus you won't be able to split it easily (it *is* possible) if/when required. Database loading is awesome, but traditionally only works when moving seperate tables to seperate db servers (or putting half on two, and so on).

With the universal table idea, you would be limited to simply maintaining a RAID-like array of databases, which is nowhere near as fast.

If you site does take off and you need to use database loading techniques, you could end up having to rewrite your code for the data retrieval (if you split your models from everything else properly its not the end of the world), but you might as well invest in a solid multi-table solution now to save hassle later.

[eluser]Colin Williams[/eluser]
I've thought about this before, too, especially with my experience developing against Drupal in which everything is a "node." It makes sense for something highly plug-able and extensible and open-source, like Drupal, but it also pays to be specific, like richthegeek pointed out. If everything in your app is an "item" in this monolithic "items" table, then all you've served to do is shift database-level complexity to table-level complexity. You have moved from a database where not all tables are used to a table where not all fields are used. Should you take it a step further, you could just have one table with one field that holds serialized data (which, I believe, is how Digg works...), or maybe just one included file that holds a massive serialized object....