Database design concepts

Question

I've just started a project and need a little push in the right direction. Here is my table structure:

users         departments     sub-departments
-------       -------         -------
id            id              id
name          name            name
email
password
created
modified

posts         photos        profiles
-------       -------       -------
id            thumbnail     id
content       large         photo_id
created       created       user_id
user_id       profile_id    department_id
profile_id    id            sub-department_id

The profiles table has about 5 more fields that are inconsequential. If any of this looks a little off it probably is.

users have one department
users have one sub-department
users have many posts
users have one photo
users have one profile

What I "wanted" to do was create all my tables I needed and bring them all together under the profile table using foreign keys. A CakePHP snippet of my profile model looks like:

var $belongsTo = array('User', 'Department', 'Sub_Department', 'Photo')
var $hasMany = array('Comment');

If by now you're thinking "WTF is this crap?".. I'm right there with you. This association works in scaffolding. But I don't want to run into trouble when I really start plugging in my logic.

I'm still new to ERD and CakePHP. Should I declare everything belonging to the User and under my ProfilesController query from there like $this->Profile->User->find('all', array('contain' => ....)); ?

I'm a little lost at this point. Any help would be appreciated. How would you implement this?

Answer 1

You said:

• users have one department
• users have one sub-department
• users have many posts
• users have one photo
• users have one profile

Funny terminology...users work in one department and, in fact, work in one sub-department. Since, presumably, a sub-department is a part of just one department, you don't need to record both department and sub-department in the profile table. In fact, if you do record both, you have a complex constraint to enforce. So, unless there's something you've not told us, the department is not needed in the profile. Though, I note that there is actually no cross-reference between departments and sub-departments, so a single sub-department can apparently be associated with multiple departments - there is nothing to stop sub-department number 1 from being associated with one person who works in department 1 and another person who works in department 2. That's unusual - not necessarily wrong, but not the way most organizations work.

There's a temptation to ask "if users only have one photo at a time, and only have one profile, why separate those from the user table?", but there are some reasons for keeping them separate.

One of the key points in modelling tables is to identify the natural primary keys. The ID columns don't count - or can be counted but you need to work out what other combinations of columns must be unique. For example, in the Profile table, although there is a Profile ID, the User ID must be unique according to the rules you stated, so in fact the Profile ID is superfluous - a waste of space, in fact (twice; once for the data column, and once for the index that will be created on it). Now, if you decided that a user might have multiple profiles over time, and profiles have a valid period, or something similar, then the Profile ID column makes sense - but the last bullet point is no longer valid.

In the posts table, why do you record both user ID and profile ID? Again, it gives you a complex constraint to enforce to no obvious benefit. The profile ID is sufficient; from that you can find the user.

You say that a user has one photo, but that is not what you've modelled. You've modelled 'each profile has a photo, and a photo is only used by one profile'. Likewise, in fact, you've not modelled 'users have one sub-department'; you've modelled 'profiles have one sub-department'. Most likely that is a problem with sloppy definitions, but you need to be careful because sloppy definitions lead to sloppy databases, and sloppy databases lead to incorrect answers and bad performance.

Fix those issues and you'd be a long way towards having a more workable design. However, I'm not sure I've discovered all the anomalies that exist in this outline schema.

Answer 2

You can live test CakePHP database design concepts on CakeApp.com.

Answer 3

I have provided a sample schema / diagram for you to look at.

A few things to note. The Post model ( posts table ) has a field slug. Use the Sluggable behavior to handle creating slugs here for you.

The Department Model ( departments table ) has a field named department_id, this should be parent_id but the builder tool throws errors on that. Change it as you need it. The lft, rght and parent_id fields correspond to the Tree Behavior. Using the Tree Behavior on the Department Model will allow departments to belong to 'parent' departments arbitrarily. This prevents you from needing a sub_departments table as a sub-department is really a department with a parent_id set to another department's id.

A attached Profile to User, and Photos habtm Profile so that you can have an unlimited number of photos attached to a user. I like to keep my User Model lean, with only what is needed for the Auth Component. The Profile Model is where I would keep first_name, last_name, age, city, etc.

Use an Upload Behavior ( MeioUpload ) on the Photo Model to handle the uploads automatically.

This should be a sound starting point for you to revise your design from.

http://cakeapp.com/sqldesigners/sql/centro

password : centro