[eluser]BRK Network[/eluser]
Hi Phil,
First, thanks a lot for your DMZ library! I've read the documentation (several times) and still laughing when I see how easy it is to accomplish some complicated tasks/queries with DMZ.
Second, good luck and best wishes on your ZestyJobs project. I'm running a local membership site, and interested in global SaaS projects - and yours is very promising. I hope your project will be featured on FreelanceSwitch, WebWorkerDaily and maybe on Lifehacker - or on other related blogs which have lots of visitors.
Humble Idea: You may want to run a blog for ZJ because when I decide on whether or not use be a premium/paid member of a SaaS, I always check their blog and see how often they update their service. (A good indication on whether the entrepreneur is still working on the project or not.)
Third, I need a point of view on database design - which supports DMZ:
My project is a niche classifieds site, and the table names goes like this:
profiles
profiles_individuals
profiles_groups
profiles_lessons
profiles_cities
profiles_districts
...
constants_cities
constants_districts
constants_lessons
constants_schools
...
* Table names that start with "constants_" prefix keep the required data which is related to the profiles in some ways. (For example, "constants_lessons" has columns like id, name, keyword, description, language, synonyms, order, etc.)
* Table names that start with "profiles_" prefix keep the users' (either individual or group) profile data. (For example, "profiles_lessons" has columns like id, lesson_id, user_id, price, experience, etc.)
So, profiles_ tables are more than a join table because each user may have many lessons in his/her profile - and they can declare a price and experience year (and other details) for each lesson that they are listing on their profile... and they can change the order of each lesson on their profile - based on their priorities.
My questions are:
1) What will be the proper way of designing tables, and join tables (if needed?)
2) And how should I name Models - considering the fact that I'm planning to use table names like "profiles_lessons" and "constants_lessons"? (e.g. Should the "Lesson" model - with relevant "var $prefix" - cover "constants_" or "profiles_"?)
The questions above may all be wrong - if the database design that I'm planning is wrong in the first place... but looking forward to the ideas who are more experienced and smarter than me on solving this database design problem.
Thanks! 
------------------------------
UPDATE: I know that with small tweaks, I can convert "profiles_lessons" to a join table like "join_lessons_profiles" and use "include_join_fields", "_join_field", "set_join_field" BUT I need to do a lot of SORT BY operations with those join_fields (experience, price and order) and using _join_field does not sound like the most efficient way to me.
[eluser]OverZealous[/eluser]
@BRK Network
I don't think you are going to be able to use that kind of naming scheme with DMZ. It's probably going to give you a lot of trouble. Besides that, it sounds like you are over-complicating it.
Note, DMZ does not have any built-in method for dealing with joins between more than 2 classes. You must have a model in the middle if you want to connect more than 2. There's a note about that on the troubleshooting page.
Think about your information as objects first. Don't worry so much about the database, since DMZ works backwards from the objects. You are trying to represent something (and I don't completely understand what), but it looks very simple:
Users have multiple Lessons.
A Lesson has a price, a school, and maybe some other information.
etc.
If you instead focus on the objects and how they relate, the database is much easier to build based on the models. The database built on DMZ rules tends to describe the models and relationships very well.
This is the one major drawback to even a very fast ORM like DMZ: you are going to have to design your application and database around the framework, not the other way around. Some ORMs provide a lot more customization, but that comes at the expense of simplicity and performance.
[eluser]modano[/eluser]
Hello,
Im sorry if this is easily found in the manual, or have been discussed many times before in the forum.
I have read the manual over and over many times, and i have tried searching the forum with its search, and even google site search, but i just cant find the solution to my problem.
Here is my problem,
I have three tables:
USERS(name)
CITIES(id,city)
CITIES_USERS(id,city_id,user_id)
On my form, in my view for creating a new user, there is a textfield that asks for favourite city (more cities can be added later on hence many-to-many rel).
But when I submit the form, the error message says "The City relationship is required."
Now, in my model 'USER' I have rules that 'favorite city' (id city) is a required field, I also have the $has_many = array("city") and $has_many = array("user") variables set up properly in both models.
Why cant I get past the validation?
Grateful for any help,
modano
[eluser]OverZealous[/eluser]
@modano
Well, you still need to look up the city to save. Just typing text into a field doesn't relate the city to the user.
If you have city as a required relationship, then you need to include the city when saving the user.
If you are trying to bypass the validation temporarily, you can do ->skip_validation()->save().
[eluser]BRK Network[/eluser]
[quote author="OverZealous" date="1279934524"]
Think about your information as objects first. Don't worry so much about the database, since DMZ works backwards from the objects. You are trying to represent something (and I don't completely understand what), but it looks very simple:[/quote]
Good point.
I've just started learning and experimenting with OOP, and had more experience with traditional database design and procedural code approaches... but it looks like this is not the way to go with CI and DMZ.
Thanks!
----
To simplify what I need to achieve:
* Each user will have multiple lessons (Maths, Biology, etc.) on their profiles
* Each users' lessons will have a price, experience year and priority value. (Lets say, Joe has 3 years of experience on Maths, and his price is $50 per hour, and Maths is his #1 priority (while Biology is #2).)
* Each user will may have one group or no groups at all. (Let's say, Joe will be a member of "Eagle Eye" group, while Jane will be an independent tutor with no groups.)
* Each user will have a city.
* Each user will have many districts/areas (in the selected city.)
The above data is related with the user profile... however, we need to have tables for each constant (like lessons, cities, districts, schools, exams) and represent them with an id. Because we'll need to change the "name", "keyword", or "description" of a constant at a later time. (e.g. We'll need to rename Maths to Mathematics at a later time.)
So, considering the fact that we need to do a lot of SORT BY "price", "experience" or "priority"; it looks like it is not a wise idea to keep these values in join fields.
Any ideas based on this example?
Thanks a lot!
[eluser]OverZealous[/eluser]
@BRK Network
Your relationships look something like this:
Code: User
fields: firstname, lastname, etc
has_one
city
group
has_many
district
userlesson
UserLesson
fields: priority, price, etc
has_one
lesson
experience_year
Lesson
fields: name, etc
has_many
userlesson
(Group, city, etc, will need has_many relationships back to user, etc.)
Tables:
Code: users
id, firstname, lastname, city_id, group_id
userlessons
id, lesson_id, experienceyear_id
userlessons_users // note alphabetical order, it's important!
id, userlesson_id, user_id
lessons
id, name
group
name
etc.
Basically what I've done is created a new object, UserLesson, which ties users to lessons. It allows you to do all kinds of cool queries, like this:
Code: $user = new User($login_id);
// get all of a user's lessons
$user->userlesson
->include_related('lesson', '*', TRUE, TRUE)
->include_related('experienceyear', '*', TRUE, TRUE)
->get_iterated();
// get all of a user's Math lessons
$user->userlesson
->include_related('lesson', '*', TRUE, TRUE)
->include_related('experienceyear', '*', TRUE, TRUE)
->where_related('lesson', 'name', 'Math')
->get_iterated();
// get all math or biology lessons, and the users associated with them
$userlessons = new UserLesson();
$userlessons
->include_related('user', array('firstname','lastname'))
->include_related('lesson', 'name')
->where_in_related('lesson', 'name', array('Math', 'Biology'))
->order_by_related('lesson', 'name')
->get_paged_iterated($page, 20);
foreach($userlessons as $userlesson) {
echo($userlesson->lesson_name .
' is taught by ' . $userlesson->user_firstname . ' ' . $userlesson->user_lastname .
'<br/>');
}
Hopefully that will give you some starting points.
[eluser]BRK Network[/eluser]
[quote author="OverZealous" date="1279936836"]
Hopefully that will give you some starting points.[/quote]
Phil, thank you very much indeed for your prompt reply... and excellent starting point!
I'll certainly build upon your example.
Your time, energy and knowledge is much appreciated!
[eluser]modano[/eluser]
[quote author="OverZealous" date="1279935175"] @modano
Well, you still need to look up the city to save. Just typing text into a field doesn't relate the city to the user.
If you have city as a required relationship, then you need to include the city when saving the user.
If you are trying to bypass the validation temporarily, you can do ->skip_validation()->save().[/quote]
ok, i was hoping that DMZ would save the city for me if it did not exist, and use the ID for the city when saving the user
perhaps too much to ask for. But basically, bypassing the validation on that field is what i have to do, then create/get the ID for the city before saving the new user object?
thank you,
modano
[eluser]OverZealous[/eluser]
@modano
Simply save the city first, then save the user:
Code: $city = // look up city
$city->save();
$user = // create and setup user
$user->save($city);
[eluser]modano[/eluser]
OverZealous
yes, that was never the problem. It was just that a textfield with rule 'required' that has text put into it, did not pass validation. I guess the 'required' does check on a lot more things than just that it has text or been selected by the user. I will write a custom required rule for textfields representing fields in another table then.
correct?
|
