Require advise on table designs

[MikoMak]

Hi, I require advice on the design of a table for a project I'm working on:

The project is a room-mate matching service and so at the moment I have 3 tables, a user table for all users, a table for the Tenant (T) and one for the Landlord (L).

Since I'm using a seperate user table to hold the personal details, both tblT and tblL only hold data that is specific to their requirements e.g. rent, location etc. The specifics for each differ from the other considerably so that tblL has many more columns than tblT.

So when a T logs in they see L listings and vice versa. I'm using sessions so on login a session variable is set depending on whether it is a T or a L logging in and listings are displayed accordingly. Each user can only have 1 listing so each listing has a 'user' field with that user's unique id in that table to identify which user that listing belongs to.

To facilitate communication, I have a messaging service where a L can send messages to a T, and vice versa, after viewing a listing. (Messages held in seperate tables so not part of this problem, only mentioned to facilitate a better understanding of the issue)

When a user (either T or L) receives a message I am providing a link to the sender's listing so e.g. when a T receives a msg from a L, there will be a link to L's listing so that T can view it easily.

And this is where my current problem lies - in order to get the id of L's listing, I've had to write a if/else script that first checks the session variable to see whether it is a T or L logged in and then use the inverse to go check for the listing. In this case it would be a T logged in so my script knows to check against tblL for the landlord's listing.

This essentially makes this unscaleable as I would have to re-write that script if I ever introduce a third option in addition to T & L.

The only solution I can think of at the moment is to have only one table to hold the specific data for both Ts and Ls so I can query a single table using using the unique id as compared to first figuring out which table to query and then using the id to query.

So my queston is: is this the best way to go about it or am I being short-sighted and not seeing another, better solution? Would I be breaking convention in having a single table holding data for 2 such seperate entities?

I hope my explanation here is clear. Any questions, just yell...

Thanx in advance for any insight ....

[fenway]

I'm not sure that I understand why you can't simply have another "user_types" table, and store user_id, user_type, and type_uid. This way, you can assign anyone to anything, and you can easily scan this table to figure out what group(s) each user is part of.

[MikoMak]

Hi fenway,

Thanx for your response. Yup, I've added a column in my users table to store each users classification and it's made it all simpler.

I had to re-write some other bits of the app but it's all a lot cleaner now.

Thanx for the help!