/    Sign up×
Community /Pin to ProfileBookmark

How Would You Manage This….

Copy linkTweet thisAlerts:
Jun 15.2009

Customers will be able to pick any number of towns and cities they want to advertise in with this directory site, and of course each customer will be one of about 60 different trades/businesses…

This directory will first list all the types of trades/businesses (painter, electrician, plumber, etc…) signed up and ONLY those signed up. Then when each trade is clicked, a sub menu will come up of all the cities associated with that trade — so if there are three customers in the trade and each customer has 5 unique cities associated with them, then a total of 15 cities will display. Then when each city is clicked, the appropriate customer/customers will display that are associated with that city.

I’m racking my brain trying to figure out a database scheme to handle this. I’m thinking of having three tables (customers, trades, cities), but some fields will have to store arrays….

Can any of you share your ideas, if you have any…???

to post a comment
PHP

4 Comments(s) ↴

Copy linkTweet thisAlerts:
@svidgenJun 15.2009 — You really just ought to add two tables: [I]customer_city[/I] and [I]customer_trade[/I]. Each customer ought to be listed in the customers table [B]once[/B], each trade in the trade table [B]once[/B], and each city in the city table [B]once[/B]. And then, of course, customer_city should also have a [B]UNIQUE[/B] constraint on [I](customer_id, city_id)[/I], and customer_trade should have a [B]UNIQUE[/B] constraint on [I](customer_id, trade_id)[/I].

Make sense?
Copy linkTweet thisAlerts:
@NtrimgsauthorJun 15.2009 — I think I see what you're saying, but doesn't it change everything if each customer could have multiple cities associated with them?

The customers table would have a field called "trade" which will list one of a number of different trades. So displaying the distinct trades of the customers is easy. The next step is to filter it down to the cities associated with each trade. I figured I'd have a table called trades that would have fields of every trade and in each field would be stored an array of the cities. There would be one id field of 1. A while loop would cycle through the array of cities associated under that trade's field. So that would list the cities. The next filter would be the actual customers associated with each city. That would lead me to the city table which would have all the cities as fields and the rows would be the trades and inside would be arrays storing the customers by id. A while loop would cycle through that array and display the customer's name and lead to their individual pages.

This is the best I could think of...it just seems very cumbersome, and the idea of initiating all of these different arrays in different tables as the customers sign up almost frightens me.
Copy linkTweet thisAlerts:
@svidgenJun 15.2009 — You really ought keep your data normalized to some extent. You could omit the [I]customer_trade[/I] table I mentioned if you were to decide that each customer can only be associated with a one trade. In any case, storing arrays or other complex data in an SQL field is almost never the best way to do things.

Relationship tables (like [I]customer_city[/I]) can be a little tedious to write into, but they offer great performance and flexibility. The extra few lines of code you need to insert a relationship is well worth it.
Copy linkTweet thisAlerts:
@criterion9Jun 15.2009 — You may also want to read up on Join statements. You could easily join the 3 or more tables together and limit the results by the city without the need for all the extra looping and arrays. http://dev.mysql.com/doc/refman/5.0/en/join.html
×

Success!

Help @Ntrimgs spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 6.1,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @meenaratha,
tipped: article
amount: 1000 SATS,

tipper: @AriseFacilitySolutions09,
tipped: article
amount: 1000 SATS,
)...