Help me scheme some part of this database

Does a vendor belong to just one city?

Or can a vendor with the same name, same owner be in multiple cities? If so, then you will want a many to many most likely. We'd need more information.

@Mithridates I tend to have an addresses table that has columns for the address components (street_address, locality etc) and coordinates (latitude and longitude). Then for things that need addresses, I’ll associate an Address instance to them.

For things like vendors, I imagine a vendor could have multiple locations. Think of vendors like McDonald’s or Walmart. They’re a single vendor but they have many locations. Therefore, I can see three entities:

• Vendor (i.e. McDonalds)
• Location (i.e. Houston, TX)
• Address (the specific street and local area)

A vendor has many locations, and an address can be morphed to a location (as well as any other entities in your application that may need an address).

@mstnorris yeah you're right, I submit what info you ask.
@martinbean you mean I create a new model and table for addresses and put what ever related to addressing and navigating in it?
for example I can put address, coordination, city , etc?
right now my routing for a vendor is:

Route::get('v/{vendorSlug}','VendorsController@show');

Which just maps to a vendor name (vendor name isn't necessarily unique).
After adding possibility of being branches of a vendor in all over the city how can I identify and make a specif route for this specific vendor?

@Mithridates A vendor is an organisation, right? So for a specific venue (if you’re building a Foursquare clone) would have its own endpoint, i.e.

Route::get('vendor/{vendor_slug}', 'VendorController@show');
Route::get('venue/{venue_slug}', 'VenueController@show');

Your show vendor route could list all locations belongs to a vendor, whereas the show venue route would show displays for a particular location only.

I would kinda do what @martinbean says, with a little difference.

The question was how to associate a city with a location. Does that mean you'll have a city table, with information about that city? So you can get all lcations associated with a city?

If so, I would have three models Vendor (Mc Donalds), Location (i.e. Houston, TX, contains the address) and City (Houston, TX)

a vendor has many locations. A location belongs to a vendor, and to a city. A city has many locations.

@martinbean a vendor is a store so to speak.we can specify type of vendor with a left join of another table which is not my concern just yet.
A vendor may or may not have any branches but with this approach I should create 2 routes for every venue, even if it hasn't any branches. right?

I'm confused now :P

Isn't a vendor sort of a "brand"? Isn't a location/branch/venue the same thing? Then you could have a route for a venu/branch/location and a route for a vendor (which could give info over, for example, mc Donalds, and list all the mcdonalds locations), which is what @martinbean was saying :)

@Mithridates If a vendor’s a single store, then it is a branch affectively.

store...yet anothre name for a location/venue/branch. Lol.

In my case I have a Person with only one address information. I need a table and model ou just put all together?

@Prullenbak , @Prullenbak I don't know why this simple question really seems confusing!!
just one question, Why don't you save location table as a simple column and allocate another model for it?

@martinbean
'' @Mithridates If a vendor’s a single store, then it is a branch affectively. '' do you mean creating a venue creates a vendor also??
do you think this is efficient of storing data in the aspect of memory?

@Mithridates I think you need to have a think about what data your want to store in your database. You don’t seem to have a clear idea of what your entities are.

It's confusing because you seem to have a different idea about vendors/stores/locations/adresses/stores in your head then martinbean and I do ;)

So, once again.

Assuming that a location is a single point on earth, where you can go buy something. One (1) starbucks store for example. I would store the address in some columns on the locations table.

Questions:

1. is a vendor a brand/company, that contains multiple locations (like starbucks)?

Yes?

Create a vendors table and vendor model and give the locations table a vendor_id. This gives you the oppurtunity to simply get "all starbucks locations", and also to store extra information about the brand (description, logo, whatever you want or need).

No?

You don't need to worry about vendors, Drop it in the name column (that starbucks on 3rd street) or any other column of the locations table.

2. do you need to somehow list/fetch the details of a city, with a list of locations?

Same thing: Yes?,

create a cities table and a city model. and give the locations table a city_id.

No?

You don't need a cities model drop it in the city column of the locations table (San Fransisco)

@martinbean , @Prullenbak OK. we're getting somewhere :) First of all I describe some conventions so the conventions are same among us.
Vendor: a store, with brand,logo, etc. which May or May not have multiple branches in the city or another cities
Location: all information related to location of a specific vendor branch. it could contain information like coordination,address,also city_id.
City:a model and table related to a city. location's table city_id attribute points to a record in this table.

So till now was anything missing from definitions ??!

---

here are my models:
City.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class City extends Model
{

    public function locations()
    {
        return $this->hasMany('App\Location');
    }
}

Vendor.php

<?php

namespace App;

use Cviebrock\EloquentSluggable\SluggableInterface;
use Cviebrock\EloquentSluggable\SluggableTrait;
use Illuminate\Database\Eloquent\Model;

class Vendor extends Model implements SluggableInterface
{
    use SluggableTrait;
    protected $sluggable = [
        'build_from' => 'name',
        'save_to' => 'slug'
    ];
    protected $fillable = [
        'name', 'address',
    ];

    public function location()
    {
        return $this->hasMany('App\Location');
    }

}

Location.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Location extends Model
{
    public function city()
    {
        $this->belongsTo('App\Location');
    }

    public function vendor()
    {
        $this->belongsTo('App\Vendor');
    }
}

Do these definitions are as it is expected ??!

@Mithridates I’m still unsure why you need city as a model? Surely if this is an application displaying locations on a map, all you really need is the latitude and longitude of a point?

@martinbean, I think it has benefitials over saving a string in location.(Correct me if I'm wrong)
first of all, I can filter city names easily
second,database normalization
some other fields can be added to cities along with city name.
anyway, what's your suggestion?

@Mithridates I just store cities as a string. What’s stopping you from queries like this?

SELECT * FROM `venues` WHERE `city` = 'Houston' AND `country` = 'USA';

I don’t see why it needs its own model.

@Prullenbak what's your idea on clearing city model?
@martinbean doesn't it affect database normalization?
or for example I can store statistics for a city

@Mithridates Just index your column. I think it’s redundant to store city in a separate table/model if all you’re saving is literally the city name.

@martinbean,@Prullenbak thanks, the location related fields,tables are almost solved now.
what about vendor and venue. you know vendor is an organization (like McDonalds) and a venue is a store of it.
also a vendor and venue can be same for example for a simple restaurant which hasn't any branch.
how do I specify this?

@martinbean,@Prullenbak any help??

Like I said, I would have a table for a vendor (or brand), with a logo, name, description, etc), then I would have a table for each location (or venue), with an address (you can store the address in a seperate table if you want, but I don't see why)

About cities: If you only store the name of the city, then a separate table is not necessary at all. It might be a little bit easier to have them in a separate table, in case you might want to do things like "other venues in this city" or stuff like that. Sure, you could do what @martinbean said:

SELECT * FROM `venues` WHERE `city` = 'Houston' AND `country` = 'USA';

But I think that relies on the correct spelling of the city names, too heavily. Your choice :)

@Prullenbak, @martinbean yeah All of stuff for addressing and location is solved thanks.
But you know, suppose I created a vendor table. and added McDonald in a record.
This time a user wants to add a restaurant how can he claim that he is a branch of this vendor or just another independent restaurant.
could I give the point?
@Prullenbak thanks so much for your time allocation. Can we have 2 minute chat for this problem?

I guess you could give users the option to choose from the list of vendors, or let them create a new one (even an independent restaurant is "part" of a vendor (a very small one :) ) with a logo, name, etc.

You could also make it optional. But then you will have to check the existence of the relationship every time you use it. That might not be ideal.

Sure, we can chat all day about the cities "problem", but in the end it's just a choice. If you're not storing any more info about a city than the name, just put it as a string in the locations table for now. You can always change it to a separate table later.

@Prullenbak this is my email : president.mehrdad [at] yahoo [dot] com email me with your yahoo mail.
I will be in touch with you.
Going to implement more!

@Mithridates You seem to be building a Foursquare clone. I’d ask myself: Why?

@martinbean not a clone. But most features are alike. It's just for hobby! Should foursquare stay exclusive?!