Is this database layout correct?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
aditya2071990
Forum Contributor
Posts: 106
Joined: Thu May 22, 2008 11:30 am
Location: Hyderabad, India
Contact:

Is this database layout correct?

Post 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...
Attachments
This is the attachment in which the layout is there...
This is the attachment in which the layout is there...
db.gif (33.32 KiB) Viewed 297 times
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Is this database layout correct?

Post 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.
User avatar
aditya2071990
Forum Contributor
Posts: 106
Joined: Thu May 22, 2008 11:30 am
Location: Hyderabad, India
Contact:

Re: Is this database layout correct?

Post 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!
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Re: Is this database layout correct?

Post 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.
User avatar
aditya2071990
Forum Contributor
Posts: 106
Joined: Thu May 22, 2008 11:30 am
Location: Hyderabad, India
Contact:

Re: Is this database layout correct?

Post 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!
User avatar
aditya2071990
Forum Contributor
Posts: 106
Joined: Thu May 22, 2008 11:30 am
Location: Hyderabad, India
Contact:

Re: Is this database layout correct?

Post by aditya2071990 »

Oh my god, I can't believe I asked this, this was SO long ago!
Post Reply