Most Efficient Join

Hi all,

I have a user table and an object table (for project and properties at
the moment). I can get a list of all my Project Objects with the user
that created them by doing this...

select * from tbl_object join tbl_user on tbl_user.user_id =
tbl_object.created_by where object_type = 1 #1 being project type

Lovely.

Now, technically a User is also an object (as is just about everything
else in our application), but how efficient is it to have 'everything'
in the one table and keep joining that to itself?

select * from tbl_object tbl_object_project
join tbl_object tbl_object_user on tbl_object_user.value =
tbl_object_project.created_by
and tbl_object_user.object_type = 5 #user type
where tbl_object_project = 1 #1 being project type

Part of me thinks that a user is an object and should be in the object
table, and another part thinks it is just too clunky to keep joining
tbl_object to tbl_object.

What are you thoughts? Which is considered "technically" correct? Has
anyone been down this road before? And, probably the most important
question - which method is the most efficient?

Eventually there will be a lot of data in the db, so efficiency will
become more important.

Many thanks,

Amy

amykimber@gmail.com wrote:
> Hi all,
>
> I have a user table and an object table (for project and properties at
> the moment). I can get a list of all my Project Objects with the user
> that created them by doing this...
>
> select * from tbl_object join tbl_user on tbl_user.user_id =
> tbl_object.created_by where object_type = 1 #1 being project type
>
> Lovely.
>
> Now, technically a User is also an object (as is just about everything
> else in our application), but how efficient is it to have 'everything'
> in the one table and keep joining that to itself?
>
> select * from tbl_object tbl_object_project
> join tbl_object tbl_object_user on tbl_object_user.value =
> tbl_object_project.created_by
> and tbl_object_user.object_type = 5 #user type
> where tbl_object_project = 1 #1 being project type
>
> Part of me thinks that a user is an object and should be in the object
> table, and another part thinks it is just too clunky to keep joining
> tbl_object to tbl_object.
>
> What are you thoughts? Which is considered "technically" correct? Has
> anyone been down this road before? And, probably the most important
> question - which method is the most efficient?
>
> Eventually there will be a lot of data in the db, so efficiency will
> become more important.
>
> Many thanks,
>
> Amy
>

Amy,

Well, when you get right down to it, everything in the world is an
"object". But that doesn't mean you should keep automobiles in the same
table as tuna. IOW, you can abstract something too much. You need to
look at the attributes of each object.

Without more information on what your "objects" are, it's difficult to
tell whether things should be in the same table or not.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================