Classifieds Database Help please 2

[jdm3]

I am looking for how I should build my databse for a classified ads. I have a map made in flash that pushes variables, that when a user selects a "country" then a "State" and then a "City" he could place an add under a category he choses, lets call it "Transportation" then chosing a sub category "Automitive" he then could place his ad and upload a picture. but must have selected his city in order to place the AD, how would I build my databse to accomplish this, and make the Graphics MAP and the Categories work together, now when the user wants to do a search for an Automobile, he could do a search once clicked on the COUNTRY map, and chosing the subcatergory "Automobile", the search would pull all automobiles from that country, and if he clicks the map again chosing the STATE, the search would then Show all Automobiles in that State , or even again clicking the CITY the search would show the automobiles in that CITY.
I am New to this so I hope this makes sense to get a response.

 

[lespaul]

Here's a link that might help you get started. As long as you develop your database properly, you can implement any type of interface over it that will accomplish what you want.

http://www.alphabetcitydataworks.com/Developers/FundamentalsOfRelationalDatabaseDesign.doc

HTH

Leslie

 

[jdm3]

So here is what I have created if this is what is the meaning, in the "databse.mdb" I have 5 tables,
tblCountries with countryID, countryName
tblStates with stateID, stateName
tblCities with cityID, cityName
tblAds with adsID, adsName, adsPrice, AdsCategory and adsPicture, adsBreifdes, adsFulldesc, adsStatus
tblUsers with userID, userName, userPassword, userEmail
Would this be the properway top building the tables, and also once doing the Relationships in what or should I do the relationships

 

[jdm3]

Would this be the proper way to build the tables, and also in what order should I do the relationships

 

[lespaul]

I think in your tblAds you need the country,state,city ids so that you know where the ad is from and add UserID to the tblAds so you know what user the ad came from.

Once you add those fields open the relationship window and connect the tblCountry CountryID to the tblAds CountryID and do the same for state, city, and user.

You should also have a tblCategories which joins into the tblAds.

Hope that helps!

Leslie

 

[jdm3]

Thanks for the reply, just to confirm
Connect
tblCountry CountryID to the tblAds CountryID then
tblState StateID to the tblAds StateID then
tblCity CityID to the tblAds CityID then
tbUser UserID to the tblAds UserID

you mentioned above"You should also have a tblCategories which joins into the tblAds."

I have the Category in the "tblAds" you mean I should have a seperate table for tblCategory and then tblSubCategory

then would the relationship be

tbCategory CategoryID to the tblAds CategoryID
tbSubCategory SubCategoryID to the tblAds SubCategoryID

 

[lespaul]

The first part of your clarification is correct. In the relationship window, join all those tables to the tblAds.

In the tblAds you have a field named AdsCategory, this should be a foreign key to a table named tblCategory that stores all the valid categories an Ad can be placed into.

So you'll have
tblCountry: countryID, countryname
tblState : stateID, statename
tblCity : cityID, cityname
tblCategory : categoryID, categoryLngDesc, categoryShtDesc
tblUser : userID, username, useremail, userphone, useraddress, etc.
tblAds : adsID, adsName, adsPrice, AdsCategory(FK), adsPicture, adsBreifdes, adsFulldesc, adsStatus, adsCity (FK), adsState(FK), adsCountry(FK), adsUser(FK)

all you will store in the tblAds is the ID from the related field.

HTH

Leslie

 

[jdm3]

Thank You very much for your help I realy appreciate your input.

would I also need a table for the SubCatagory or everything goes into the tblCategory

in the tblCategory you have
tblCategory : categoryID, categoryLngDesc, categoryShtDesc
should "categoryLngDesc" be the same as in the tblAds instead of using "adsBreif" calling it "categoryShtDesc"

And when you mention (FK) that would be as "Number" (Long Integer) under DataType.
Thanks

 

[willir]

jdm3

Actually, one thought to ponder...

Can the same ad be posted in different papers and/or different cities, etc???

For example, can the same ad appear in New Yor Times, Chicago Tribune, Boston Globe etc?

If so, then you have a many-to-many (M:M) relationship. We already know one paper can have many ads. But can one ad appear in many papers.

If this is true, then you need to create a join or intermediaary table.

If we use the City for the M:M relationship, then your affected tables would / could look like...

tblCity :
cityID,
cityname

tblAds :
adsID,
adsName,
adsPrice,
AdsCategory(FK),
adsPicture,
adsBreifdes,
adsFulldesc,
adsStatus,
adsUser(FK)

tblAdProfile - link Ad x City
adsID (FK),
adsCity (FK)

Works like this...

adsID adsCity
1 1 New York
1 2 Boston
1 3 Chicago
2 4 Texas
3 1 New York
3 5 Rhode Island
3 6 London (UK)


Next point...
You may want to drop the State and Country codes from the tblAds. State and Country codes describe the city. Yes, there is more than one city called London (eg: UK and Canada). But if the user enters the city + state + country, you may end up with nonsence such as Venice + Utah + Italy. When they select the city, the combo box should display Venice + Roma (my appologies to the Italians, I do not know what provence Vince is located in) + Italy.

Per your question on categories...
A category table can handle this, and will had future flexibility. Add a new field...

tblCategory :
categoryID,
categoryLevel - primary, secondary, tertiary / or main, sub ...
categoryLngDesc,
categoryShtDesc

You can add a new level when ever required.
This will not affect your data since your still have the categoryID.
You will have to hardcode a bit to account for the different levels.
This does break a rule of normalization but saves creating a table everytime you want to create a new level.
If required, you can also use this table to create a M:M relationship if you decide that ads can have multiple categories (although this would be mroe work).


Last point...
Do you want to track what media was used to post the ad? For example a newspaper, etc? If so, I do not see this info captured in this thread.


HEY, Leslie. Good to see your posts. I will get back to you regarding SF later -- I am currently tied up with some technical issues.

Richard

 

[jdm3]

Hello Richard and thank you and also Leslie for your input, Richard here is a breif discription of how this will work, the user as an exemple will not be entering any Cities or Province, as a text or any dropdowns, the User shall be clicking on a map, he will chose his country, then the Country map will appear, he can then choose the State or Province that he needs, If he is doing a state wide search he would search in the Search feild only Ex: "1965 Corvette"
and if a match is found in that state it will appear, If he wants also to do the search accross the entire country then he should be at the country Map wich will pull out all the 1995 Corvettes in the country, but if he wants to look closer he may click the province map then clicking on a city of choice his search would be specificly for that city. Then while at the city level, he can then do a search for any other category that he wishes to do either another car or a for an apartment. I also want it to work as if you chose on the category menu Automobile Dealerships and Chosing Toyota then all Toyota dealerships ads would appear for that City, at whatever map level that he choses, But in Order for the User to place an Ad he must chose the City that he wants the Ad to appear, if he tries to place an Ad when he only choses the Country or Province/State then he cannot place an Ad until he choses his City.

 

[jdm3]

So that way lets Say
Country Canada = ID 1
Province Ontario= ID 1
City London- ID 1

That way for
UK ID might be ID 25
London ID 1 since London ID 1 is Associated with UK ID 25 shouldn't this eliminate that problem. I would be happy if it would.

Or should I have a new Table for every Country

 

[willir]

jdm3

I still thinking about this ... I can see how intuitively you want to go from country -> -> city which leads to the intuitive approach of including the country ID on the ad record. But it seems to break a fundemental rule on normalization.

As per having a different table for every country - I don' thing so. I feel Leslie was quite correct in defining a table for the country.

I will get back to you.

Richard

 

[jdm3]

Vwey well, I am looking forward to your responce.

 

[willir]

JDM3

I am back.

I attempted to write your design on te white board (where I do some of my thinking), and I quickly came to an idea...

I realize you are looking at the problem from the customer's perspective. Select a country, maybe select a state or provence, maybe select a city.

Basically, you have the potential of a many-to-many--to-many relationship. Let me explain.

An Ad can appear in many coutries (unlikely of course, unless Canada / USA / Mexico).
A country can have many ads.

An Ad can appear in many states or provences.
A state or provence can have many ads.

Ditto for cities.

So you can create a intermediary or join table...

tblAdCntryStateCity
AdID
CountryID
StateID
CityID
Date

A record would be entered for each country or State or City that is applicable for the ad. The query would then check for the country, state / provence and city as required. It would work.

OR
We can put some common sense and use Leslie's initial design where the Ad record itself stores the country + state / provence + city.

This would result in more than one record created for an ad distributed to multiple sources, but I suspect this would be a rare event.

To prevent spelling mistakes and errors I still think you need supporting tables. For example, "St Johns", "St. Johns", "St. John's".

You can create simple tables for country, provence / state and city.

tblCountry
CountryID
Country

tblState
StateID
StateProv

tblCity
CityID
City

This is a fairly simple solution.


To avoid the London UK and London Canada type of thing, and a more normalized solution would be

tblCountry
CountryID
Country

tblState
StateID
CountryID
StateProv

tblCity
CityID
StateID
City

You can then use the city record to back track to the state/provence and country.

Richard

 

[jdm3]

tblCountry
CountryID
CountryName
tblProvince
ProvinceID
ProvinceName

tblCities
CityID
CityName


What if I was to ad a "tblAssociationLocationID"

tblAssociationLocationID
CountryID
ProvineID
CityID

tblCategory
categoryID
categoryLngDesc
categoryShtDesc

tblUser
userID
username
useremail
userphone
useraddress, etc.

tblAds
adsID
adsName
adsPrice
AdsCategory(FK)
adsPicture
adsBreifdes
adsFulldesc
adsStatus
adsCity (FK)
adsState(FK)
adsCountry(FK)
adsUser(FK)

What are your taughts on this structure.