HasOne relationship

Any Idea? :X

Hey @kfirba: From your description, I would approach it like:

User:

hasOne('Profile::class')

Profile: (with a user_id field on the table - which is a foreign key that references id on users)

belongsTo('User::class')

hasOne('Address::class')

Address: (with a profile_id field on the table - which is a foreign key that references id on profiles)

belongsTo('Profile::class')

@goardshark but that somewhat doesn't make sense. If you think about it, I can potentially create 2 profiles with the same user_id which means that one user may have many profiles and not just one. If we use the profile_id on the user then a user can truly only have 1 profile associated with him.

Also, the Address model is also shared with another entity so I guess I will have to use the belongsTo relationship for the address on the profile so I can reference an address from both a Profile object and a Store object right?

@kfirba, I understand what you're saying about potentially having 2 profiles with the same user_id, but based on that logic, are you not concerned about two users with the same profile_id. :) You could take care of these concerns with validation (like a Request validator) on the front end specifying that the user_id (or profile_id, whichever way you're going), must be unique. Does that fit into what you're doing? I tend to chain things together from User model southbound by referencing the previous table in the chain; i.e. I always reference user_id in the table down the line from it instead of whatever_id in the User table. That said, I'm still just a baby Laraveller, so I could be looking at it all wrong. How's that for a disclaimer? :)

@goatshark It actually has nothing to do with Laravel (the DB design). I respect everyone who has an opinion doesn't matter if it's a beginner's one or a more intermediate developer's one. I just think that the naming convention is kinda off. I also do understand your points where a 2 users can potentially have the same profile. However, it doesn't violate logics since we still obey to the rule where a user may have one and only one profile which can be potentially be shared with multiple users.

What's your opinion as for the Address model being shared with both profiles and businesses? It means that I can't "host" a foreign key on the Address table since it may belong to a business or a profile

Then your address table should use polymorphic relations. You can name the related column what ever you want. If you don't set it in the relationship it defaults to what your issue is.

@kfirba, if a profile can be shared with multiple users, but not the other way around, I totally agree that the logic stands unbroken.

As for connecting an address to a profile and also to a business, and without getting into @jekinney's suggestion about using polymorphic relations (because I'd need some minutes of reading before even beginning to speak to that), I would default to a profile_id and a business_id field on the address table and calling those both foreign keys.

I'm curious as to what your functional use for some of these are. I "get" users. I get a user having a profile. To me, the word "address" makes me think that a user would have an address, and have a profile separately. Also, I'm not sure what the use for 'business' is. Not that I need to know these things, just curious because I'm wondering how much my answers are being tailored to my interpretation of what your table names are - and maybe not based on your actual intent.

@jekinney I try to avoid polymorphic relationships. They bring more troubles than they actually help. What is wrong with having an Addresses table with an id as a primary key and then have 2 other entities, profile and store use that. To do so, we store a address_id on both profiles and stores tables and we get the same end result without having to deal with polymorphic relationship right?

@goatshark Well an address is obviously a part of a user's profile. The reason it has been extracted to its own table is because a store/business may also have a profile. I had 2 choices:

1. Repeat the address columns in the profiles table and in the stores table
2. Create a addresses table and have address_id foreign key on both profiles and stores so now if I decide there is another field that an address needs, for example, country or region, I can add that in once place (the addresses table) and have it reflected on both my stores and profiles.

I've chosen the latter option cause it seemed more reasonable for me I guess :x

Hey @kfirba, wow, there are a lot of options here. Is there any relationship between Store and User? Or are those two separate entities that both happen to have (or be able to have) a Profile? I ask because if a User and a Store can have their own profiles, then I would be tempted to consider:

1. A user_profiles table separate from a store_profiles table/model. They could each use hasOne Address::class.
2. Alternatively, maybe a field on the profiles table indicating whether it is a User or a Store. Like, a profile_type field (boolean) that indicates if that profile is a user or a store. It would still be able to use hasOne Address.

?? Thougths?

The more I think about how you're structuring this, I'm wondering if your approach isn't best - profile_id field on User and Store. Then, I think, you can use hasOne Profile::class on User and Store - and then, in the other direction, two belongsTo methods on Profile pointing to each of User and Store. Again, Address and Profile can keep their relationship where a Profile hasOne Address and Adress belongsto Profile. I might need to re-caffeinate though...

@kfirba @goatshark

store //company
    id - integer
   // company fields as required

profile
    id - integer
    // profile fields as required

address
    id - integer
   // address fields as required
    addressable_id - integer
    addresssable_type - string

class Address extends Model
{
    public function addressable()
    {
        return $this->morphTo();
    }
}

class Profile extends Model
{
    public function address()
    {
        return $this->morphMany('App\Address', 'addressable');
    }
}

class Store extends Model
{
    public function address()
    {
        return $this->morphMany('App\Address', 'addressable');
    }
}

Very easy, more efficient and easier to query. Not sure why you would want a bunch of null fields (in MySql null fields take up as much memory as a if the field was full) then as I mentioned you have to query for null checks or else get a fail because you returned null. Later you want to add where a user or company can have many address very simple update, or another place needs to store address another simple update without changing the core Sql schema. Plus Laravel really does 99% of the work. With the above relationships set you query with eloquent or query builder just as you would any other relationships including eager and lazy loading.

Also you can't set foreign keys on null, so you lose that performance to.

Is anything wrong with it? Your app, your the Artisan, so as long as it works.... No matter your choice there will always be "There is a better way" so to speak. Don't let your self be closed minded because of a small challenge though.

What's the real relationships between a Profile and an Address? Is it one to many or many to many?

I am asking because It seems that realistically you can have 2 or 3 profiles from the same address. (either friends or family) Also, a profile can have multiple addresses. (One main place to stay and another one for vacation)

When deciding on the relationships, is it important if you'll need to display the data too or? (You may never display how many profiles were from the same address)

Also you can't set foreign keys on null, so you lose that performance to.

I believe you can set them to be nullable if that's what you are talking about.

@kfirba, @jekinney - okay, so I just read through the laravel docs on polymorphic relations (which I've never done). Uumm, yes, I agree, exactly that. Most definitely exactly that. Thanks @jekinney. Learn something new every day, check.

@goatshark

Thank you, I agree before laravel I was polymorphic what???? But I tried it out on a test app and then was, wow...

@Ruffles Yes, not 100% positive with out double checking the MySQLdocs, but I know I have had foreign key can't be null MySQL errors during testing.

@Ruffles Your right. As long as the pk on the referenced table is set to allow null (can't do that with auto increment pk) then the related key can be null. So you have to set a different pk other then the default increment column.

@jekinney thanks for your answer. The relations are as follows: a user has one profile. A profile has one address. A store has one address. I guess the address table is more of a meta table.

Doesn't it seem a little bit redundant involving polymorphic relationship? I can solve that issue without it I guess..

Also, there won't be bunch of null values. This is the scheme:

User: 
- id
// the rest..

Profile:
- id
- user_id
- address_id
// the rest

Store:
- id
- address_id
// the rest

Address:
- id
// the rest

Does it make sense? I can associate one address with either store/profile. Also, I want to ensure that an address is private for each profile/store. Even if there are 2 people who live in the same house I would like to store this as 2 separate rows in my table.

In my models, I can use the belongsTo relationship:

class Profile extends Model
{ 
    public function address()
    {
        return $this->belongsTo(Address::class);
    }
}

Right? Or maybe I'm actually missing something?

@Ruffles The relation between profile and an address is one-one. As I mentioned above, even if 2 people have the exact same address I would like to store each address in its own table row for each user (so basically we will see 2 identical rows with different ID). The only way I'm going to access the Address is when I display the user's profile or when I show the stores data. I probably will never access the profile or store entities from the address entity.

Maybe because it's meta data there is somehow else I can store it? I just don't want to duplicate the address columns on 2 different tables.

Just wanted to add this resource for future reference - http://matthewhailwood.co.nz/visualizing-laravel-relationships/#oneToOne

@kryptonit3 that's actually a good resource haha. Thanks for the reference. I'm still not sure about the polymorphic relationship suggested here. In my kind of solution I "say" that a profile belongsTo an address while it is not true, an address belongs to a user and not vice versa. I guess I will have to check this one out :/

Any insights will be perfect

The polymorphic isn't really redundant imo, thought it isn't a one to one relationship. Which case you'll have to perform a check to see if a user or store already has an address to ensure they don't create more then one.

The _type holds the model name and _id holds the primary id. So when you query lets say all stores addresses it is simple code. Store::with('address')->get(). The rest of the query is handled with laravel. To return a single address a query scope could be used with a closure to get the first().

Like I said, in my opinion in the long run easier and easily expanded or updated later.

@kfirba your last approach is perfect for what you want to do I dont get what troubles you. You dont care if belongs to doesnt really mean belongs to in your app the main point is to have $profile->address.

@jekinney I found out that there is also a morphOne relationship which isn't documented.

@pmall So I should keep it as my latter example and not use a polymorphic relationship? I'm kinda not sure now which way I should go now.. I guess both ways will work but I'm looking for the better one in terms of maintainability and scaling.

@kfirba there is no point of using polymorphic relationship here. It is useful for 1 or many to many relationships.

@pmall I guess you are right. I will just stick to the belongTo relationship on my profile object to retrieve the address.

It is easier to show when you need polymorphic : Lets say you have products and acticles, both have many images. You want to store all images in one table (obviously the right way). Here you put product_id or article_id as foreign key in images table ? No, you store imageable_type and imageable_id.

If the relationships are many to many then you can choose to have two pivot tables (and as many pivot tables as imageable entities) or to put everything in a polymorphic pivot table.

@pmall @kfirba

Pretty explicit https://laracasts.com/lessons/polymorphic-huh

With that being said, no it's not always the answer. Reality check shows you have a blog, pages, carousel and image gallery for your users using one image table probably not the most efficient use. Bear with my example, you could end up with a million rows in your image table relating to a few thousand rows in each related model. Probably want to break that down.

Hope it helps, got a new client so wish you the best!

Bear with my example, you could end up with a million rows in your image table relating to a few thousand rows in each related model.

Databases are made to handle such numbers :)

@pmall very true :)