Page 1 of 1

Is this database layout correct?

Posted: Thu May 22, 2008 12:38 pm
by aditya2071990
Hi everyone,

I am developing a classifieds website using php and mysql and this is my first major project... I designed a layout for the database and the image is attached to this question, just have a look....

This is how the db works...I am telling this so that people will have an easier time understanding the layout:
1.'Listing Table' is the main table. This is the table I am going to depend upon to display and/or categorize my listings...
2. Using Listing ID, I'll be able to track the user info of the person who posted the listing, and the 'Type' of customer he is (Premium, Silver or Free)
3. I'll also be able to track which sub-category it belongs to, and to which category *that* sub category belongs to.
4. Also will be tracked the price range of the property (this is a real estate classifieds), and the 'Class' (Buying,selling or renting are classes).
5. Is this the right way to do it? Are there flaws in my layout? Please do answer these questions for me, as I dont have a know-it-all boss to guide me thru...I am pretty much on my own, and of course I gratefully depend upon angelic souls like you to help me out....

Please do have a look and tell me what you think about it....thanx...

Re: Is this database layout correct?

Posted: Thu May 22, 2008 8:06 pm
by califdon
You're on the right track, but you can substantially simplify your structure. You only need an intermediate table when there is a many-to-many relationship between 2 tables. Thus, assuming that a user can only be of one type at any given time, you can code the type-id right into the user-info table and eliminate the user-type table. Similarly, assuming that a listing can only be of one subcategory at a time, you can code the subcategory-id right into the listing table and eliminate the listing-subcategory table. It looks to me like that's also the case with listing-range and listing-class. If any of these attributes could be many-to-many, that is, if a single listing could be in more than one class at the same time, then your layout is appropriate.

Re: Is this database layout correct?

Posted: Thu May 22, 2008 10:50 pm
by aditya2071990
Thank you so much for the advice Sir :D ...I guess I was being overly careful with so many intermediate tables...I'll definitely remove the unnecessary ones... now, I can breath a great 'sigh' of relief and go ahead with my first major project(and then I'll be able to brag..I did it on my own! But of course, I'll mention the good samaritan who guided me back when I was lost...)

thanks again sir, have a nice day!

Re: Is this database layout correct?

Posted: Thu May 22, 2008 11:21 pm
by Zoxive
You should check out MySql workbench, a very handy tool for maping out your databases.

I uploaded an example of what it looks like here.

Re: Is this database layout correct?

Posted: Thu May 22, 2008 11:34 pm
by aditya2071990
Zoxive wrote:You should check out MySql workbench, a very handy tool for maping out your databases.

I uploaded an example of what it looks like here.

Thanks for the pointer buddy!

Re: Is this database layout correct?

Posted: Sat Oct 11, 2008 11:13 am
by aditya2071990
Oh my god, I can't believe I asked this, this was SO long ago!