Maker your own Database... Experience?

Status
Not open for further replies.
bobsoap

bobsoap

Together we can do anyone
Sep 16, 2008
605
16
0
soapdesigned.com
So I'm getting more and more familiar with MySQL these days. I've been doing frontend development for quite a while now, but I'm just beginning to venture into backend stuff more (very exciting).

As for the question - for a project of mine, I'm asking myself: how should I organize the database so that it makes sense and is as fast as possible?

Let me draw a picture:

- users can sign up (account)
- each user can create multiple "projects" within their account
- each project can contain various single folders, or "project sheets"
- each sheet contains a bunch of data/content.

So how do I best organize the database for this? I'm thinking:

1. table "users"
2. table "projects", which holds all projects across the board and has a field "user" to identify the user ID for this project
3. table "sheets", holding all sheets of every project across the board, assigned to a project with a field "project ID".

Is this the best way to do this? Down to which level do you usally create a separate table for things? I'm open for ideas or suggestions and would appreciate any link on the subject if you have one handy.

Thanks. +rep for good answers.





 


1. Your design is alright, just use proper variable names for fields, especially for key fields.
2. You may want to keep the login id/password information in a separate table.
3. Write down all functionalities and look at them to see if you need more tables (e.g.: if you will have different account types with different levels of access, that information can be stored in another table)
 
  • Like
Reactions: bobsoap
Your table setup looks fine - thats pretty much the standard convention these days - a good set of guidelines to follow is 3NF (Third Normal Form) which you can find info about on Google, heres a couple I just found after a quick search...

Third Normal Form (3NF) - Normalising Your Database | Database Solutions for Microsoft Access | databasedev.co.uk
Page 4 - Database Normalization

As I said above, these are just guidelines, if you feel like they are overkill, there is also 2NF and 1NF - either way, they are a decent set of do's and dont's that can save you time down the road.

Also, being that your setup and 3NF are all pretty much standard allows you to use a variety of off the shelf database abstraction layers like Propel, Outlet ORM, Doctrine, or ones that usually come inside PHP frameworks and they will handle the majority of queries and joins for related content.
 
  • Like
Reactions: bobsoap
Thanks guys, these are great tips. So it's better to spread out data over multiple tables rather than trying to consolidate everything into as little tables as possible. +rep coming.

Lazy, the links are awesome. Thanks a lot. I will definitely look into the db abstraction layers as well.

erect - lol. That's why I'm trying to get it organized beforehand :)
 
The idea of spreading tables out instead of combining them is important because you would probably want to add features in the future. This will allow you to go from a 1-m relationship to an m-m relationship if you need to.
 
24wst47.png


Just a quick mock-up, might help?
 
  • Like
Reactions: bobsoap
Or, you can do flat PHP database based off cookies for authorization validation :O -- It's bad management, but SQL is a cunt sometimes.
 
GrindHard said:
Or, you can do flat PHP database based off cookies for authorization validation :O -- It's bad management, but SQL is a cunt sometimes.
Click to expand...

I know where you're getting at, I've done a few projects this way too (PHP arrays instead of a db). I assume you don't mean storing actual core data in cookies though.

Then there is the option to store data in files - only that in this case I see a big security issue.

The problem is, these are not the best alternatives and can't replace the functionality of a real database. Maybe MySQL doesn't have a learning curve as flat as e.g. PHP does, but I believe it's well worth it, and I enjoy a challenge :)
 
Yay!

I was hoping for this, as Visio is a bitch if ya just want something quick!

::emp::
 
Mysql isn't really that hard to learn. I am horrible at coding and wrote a decent php mysql email system in less than a week and I had never touched mysql before (or any other "real" database). Nothing spectacular or anything but handles all of the data associated with my emailing and distributes the load across a bunch of different domains and IP addresses.

Learning to use a database like mysql will open up a lot of new and interesting things that you can do with your code that are simply to cumbersome to do with flat files and php.
 
I have a feeling that the amount of time you would spend attempting to create flat file DBs or some other system for managing storage of data would be way better spent learning MySQL, PostgreSQL, SQLite, CouchDB or some other DB system. CouchDB is different from the other 3 in how it works, but it still provides a standard API for the creation & retrieval of stored data.

Given the amount of apps using MySQL (Wordpress, Joomla, just about any other god damned CMS, Prosper, etc) it seems utterly retarded not to at least learn MySQL, your going to run in into at least 1 time (every 10 minutes)
 
might be worth keeping project as a separate table and have a userProject hashmap to join user and project, just in case you ever want to allow multiple users to co-operate on a project.
 
  • Like
Reactions: bobsoap
My favorite format:

  • Database
    • data
      • id (auto increment integer)
      • myfield (integer)
      • otherfield (integer)
      • somefield (integer)
    • myfield
      • id (auto increment integer)
      • data (varchar)
    • otherfield
      • id (auto increment integer)
      • data (varchar)
    • somefield
      • id (auto increment integer)
      • data (varchar)
Instead of formally inserting, call getVariableId("myfield", $value);
That function will (no matter what) return the id in table "myfield" where "data" is $value.
So it calls a select. If the value doesn't already exist, it inserts it into the database THEN gets the id and returns it.

That way the data table(where most of your sorting is going to be) is only dealing with numerical data and it can sort much faster, plus if you're frequently reusing data it can all refer to the same location in the "myfield" table. ("id" in the "myfield" table corresponds with the interger in the data table, "data" in "myfield" corresponds with the value that would otherwise be in the data table).

It's damn fast, efficient, expandable, and doesn't get sloppy as fast. Plus since everything but the "data" table only has 2 fields (id and data) it's really easy to consistantly pull the data without having to constantly be checking the field name or w/e.
 
  • Like
Reactions: bobsoap
Charcoal said:
might be worth keeping project as a separate table and have a userProject hashmap to join user and project, just in case you ever want to allow multiple users to co-operate on a project.
Click to expand...

A table to store user_id and project_id will allow many to many relationships.
Users will be able to have more than one project and projects will be able to have more than one user.

Not sure if that's what you meant. But I would probably take the user_id out of the project table. It might be faster or more efficient to put the user id in the project table(I'm not sure) but you're painting yourself into a corner of having to have one user per project.

Of course doing this means more joins.
 
LogicFlux said:
A table to store user_id and project_id will allow many to many relationships.
Users will be able to have more than one project and projects will be able to have more than one user.

Not sure if that's what you meant. But I would probably take the user_id out of the project table. It might be faster or more efficient to put the user id in the project table(I'm not sure) but you're painting yourself into a corner of having to have one user per project.

Of course doing this means more joins.
Click to expand...
Well, keep in mind I don't release projects, so I'm ok with that. But it'd be pretty trivial to add a userid field the the "id, data" tables.
The concept is more important than the exact example imo :)
I'm no mysql wizard though. It's just what I've had the best luck with.
 
Status
Not open for further replies.
Top Bottom