database shema on flexible foreign key ?

[navotera]

what if i have relation and database schema like this : Table name :

1. user

2. company

3. cordinate | id |table_reference| table_id | latitude| langitude |

the table user and company may have value in the cordinate table... now how can i relate or make associations with that design ?

[tag]

This may sound rude, but I don't mean it that way.

Requests for teaching help are a better fit for StackOverflow, or similar, especially when the request is about relational modeling, as this one seems to be.

By opening an issue against the library, you're (a) asking the developer of the library for help, instead of the whole world of people available on support sites, and (b) saying that that the problem you're encountering is bug or feature request rather than a lack of understanding on your part.

--

The question I think you're asking is a straightforward modeling question (language is an issue, and I'm not sure I'm interpreting your question properly). Go ahead and post it to StackOverflow. You can share the link to your post here if you like.

Short answer: coordinates as attributes in user or company tables OR intermediate user_coordinate and company_coordinate tables.

[navotera]

thanks u for your suggestion, but you unfortunately miss my point. i would say it may probably because my English narrative (coz im not native English) that cause the misunderstanding in my question.

i questioning on the context of this library not questioning on general relational database design issue. I cannot say that u already have experience using that flexible foreign key database design...

let me say there is flaw that i known on your short answer solution. what if i use the cordinate not just on 2 tables ?? what if i use the cordinate for my 10 tables let say i make table ... user, company, shipping, asset or anything .. so u say i should make 10 bloated table such as shipping_cordinate, asset_cordinate .. etc... the bloated table is so very unefficient.

[treffynnon]

How can i relate or make associations with that design?

How do you plan to do a JOIN on that design? If you cannot JOIN properly in straight up SQL then Idiorm can't help you either.

If you don't want a relational structure then perhaps you might need to look at a another method of storage than the RDBMSs that Idiorm works with.

Which is pretty much just a repeat of what Tom already said. His suggestion of using StackOverflow is a good one too.

[navotera]

what i put in cordinate table is such as : row 1 : id (1) table_reference('user') table_id(20) lat(any) long(any) row 2 : id(2) table_reference('company') table_id(20) lat(any) long(any) row 3 : id(3) table_reference('asset') table_id(100) lat(any) long(any)

table_id is foreign_key based on table_reference...

does paris have some association configuration based on that such as as simply put has_one, has_many or else ?

[treffynnon]

Write a select query in SQL that contains all the joins you'll need so I can see what you're trying to do. I suspect my original comment still stands.

[navotera]

here u go bro : when i want to find user cordinate : SELECT * from cordinate where table_reference = 'user' and table_id = 20

when i want to find company cordinate : SELECT * from cordinate where table_reference = 'company' and table_id = 20

[treffynnon]

$coordinates1 = ORM::for_table('coordinate')
    ->where_equal('table_reference', 'user')
    ->where_equal('table_id', 20)
    ->find_many();

$coordinates2 = ORM::for_table('coordinate')
    ->where_equal('table_reference', 'company')
    ->where_equal('table_id', 20)
    ->find_many();

[navotera]

hmm... thank for the code... but sorry mr. that not really the answer i wish to get. i know what u write is straightly existed in documentation.

i just asking is this not available in some association method where the relational association between model (table) is not just based foreign key but with addition unique_key (table_reference). and i wish that it could be something like this :

class User extends Model {

public function cordinate() { return $this->has_one('cordinate', $foreign_key, $unique_string/key); } }

is this supported or this would be next additional new features... ? and sorry for confirming on why this is closed quickly without u guys understanding what the description of the issue is ??

[treffynnon]

hmm... thank for the code... but sorry mr. that not really the answer i wish to get. i know what u write is straightly existed in documentation.

Ok, so you wrote the wrong SQL then. Please write what you're trying to achieve in standard SQL so it's completely self explanatory.

On 10 Aug 2017 3:48 p.m., "Muhammad Hendra" notifications@github.com wrote:

hmm... thank for the code... but sorry mr. that not really the answer i wish to get. i know what u write is straightly existed in documentation.

i just asking is this not available in some association method where the relational association between model (table) is not just based foreign key but with addition unique_key (table_reference). and i wish that it could be something like this :

class User extends Model {

public function cordinate() { return $this->has_one('cordinate', $foreign_key, $unique_string/key); } }

is this supported or this would be next additional new features...

— You are receiving this because you modified the open/close state.

Reply to this email directly, view it on GitHub https://github.com/j4mie/paris/issues/131#issuecomment-321457113, or mute the thread https://github.com/notifications/unsubscribe-auth/AAD-v8eOl2ZP7rzZKUNvKPYoUxYA4d5yks5sWpm4gaJpZM4Oxp90 .

[navotera]

Im not talking about sort of query here or database design...

This class explain it :

class User extends Model {

public function cordinate() { return $this->has_one('cordinate', $foreign_key, $unique_string/key); } }

is this supported or this would be next additional new features...

Can u please not just close this issue so anyone can contribute to this discussion

On Thu, Aug 10, 2017, 17:13 Simon Holywell notifications@github.com wrote:

hmm... thank for the code... but sorry mr. that not really the answer i wish to get. i know what u write is straightly existed in documentation.

Ok, so you wrote the wrong SQL then. Please write what you're trying to achieve in standard SQL so it's completely self explanatory.

On 10 Aug 2017 3:48 p.m., "Muhammad Hendra" notifications@github.com wrote:

hmm... thank for the code... but sorry mr. that not really the answer i wish to get. i know what u write is straightly existed in documentation.

i just asking is this not available in some association method where the relational association between model (table) is not just based foreign key but with addition unique_key (table_reference). and i wish that it could be something like this :

class User extends Model {

public function cordinate() { return $this->has_one('cordinate', $foreign_key, $unique_string/key); } }

is this supported or this would be next additional new features...

— You are receiving this because you modified the open/close state.

Reply to this email directly, view it on GitHub https://github.com/j4mie/paris/issues/131#issuecomment-321457113, or mute the thread < https://github.com/notifications/unsubscribe-auth/AAD-v8eOl2ZP7rzZKUNvKPYoUxYA4d5yks5sWpm4gaJpZM4Oxp90

.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/j4mie/paris/issues/131#issuecomment-321497021, or mute the thread https://github.com/notifications/unsubscribe-auth/ABW902DsL5USYXYK-LX6WKKt90uEQn1bks5sWsnCgaJpZM4Oxp90 .

[tag]

I'll give you a longer answer when I get back to the office In a few hour; I'm on mobile right now, I'll simply say this: you're having trouble because--based on the limited info you've given us--your design is wrong. Please trust us on this. I'll offer a much longer answer later.

On August 10, 2017 at 5:53:16 AM, Muhammad Hendra (notifications@github.com) wrote:

Im not talking about sort of query here or database design...

This class explain it :

class User extends Model {

public function cordinate() { return $this->has_one('cordinate', $foreign_key, $unique_string/key); } }

is this supported or this would be next additional new features...

Can u please not just close this issue so anyone can contribute to this discussion

On Thu, Aug 10, 2017, 17:13 Simon Holywell notifications@github.com wrote:

hmm... thank for the code... but sorry mr. that not really the answer i wish to get. i know what u write is straightly existed in documentation.

Ok, so you wrote the wrong SQL then. Please write what you're trying to achieve in standard SQL so it's completely self explanatory.

On 10 Aug 2017 3:48 p.m., "Muhammad Hendra" notifications@github.com wrote:

hmm... thank for the code... but sorry mr. that not really the answer i wish to get. i know what u write is straightly existed in documentation.

i just asking is this not available in some association method where the relational association between model (table) is not just based foreign key but with addition unique_key (table_reference). and i wish that it could be something like this :

class User extends Model {

public function cordinate() { return $this->has_one('cordinate', $foreign_key, $unique_string/key); } }

is this supported or this would be next additional new features...

— You are receiving this because you modified the open/close state.

Reply to this email directly, view it on GitHub https://github.com/j4mie/paris/issues/131#issuecomment-321457113, or mute the thread <

https://github.com/notifications/unsubscribe-auth/AAD-v8eOl2ZP7rzZKUNvKPYoUxYA4d5yks5sWpm4gaJpZM4Oxp90

.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/j4mie/paris/issues/131#issuecomment-321497021, or mute the thread < https://github.com/notifications/unsubscribe-auth/ABW902DsL5USYXYK-LX6WKKt90uEQn1bks5sWsnCgaJpZM4Oxp90

.

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/j4mie/paris/issues/131#issuecomment-321506257, or mute the thread https://github.com/notifications/unsubscribe-auth/AAAlkh8Y6yoyzG2FA0Qk22D4bGN9iiExks5sWtMMgaJpZM4Oxp90 .

[tag]

Let's start at the top: You were advised to use StackOverflow (etc.) for support. Please do that, rather than make demands of @treffynnon, or continue this thread.

Secondly: "Flexible Foreign Key" is a database design anti-pattern. Doing it that way will cause you problems down the road. (Chapters 7 & 8 of Karwin's SQL Antipatterns are excellent and highly recommended.)

Part of the difficulty in helping you is that you haven't provided enough information. Another difficulty is that you've received a couple of helpful responses, but aren't listening to the answer that the issue is with your design and not with Paris.

Allow me to expound.

Let's assume for a moment that you have a User table, which I'll call u, and a Company table which I'll call c. Both have Coordinates that you'd also like to model in your database, which for the moment I'll call u′ and c′ respectively. However, the best way to model this depends on the answers to three questions:

1. What is the minimal multiplicity¹ of the relationship between u and u′? (And assuming it's the same answer, between c and c′?)
2. What is the maximum multiplicity between u and u′?
3. Are values duplicated between u′ c′? In other words, are Users and Companies likely to have the same coordinates?

¹: I'll use the term 'multiplicity' instead of 'cardinality'. The former is a UML term, and the latter is a relational modeling term. [Some disagree on which should be used](https://stackoverflow.com/questions/17877582/multiplicity-vs-cardinality) but mostly they mean the same thing.

Assuming u→u′ is 1:1..1

If all Users have one and only one Coordinate, then Coordinate should be attributes of the User table. If the same relationship between other entities and their Coordinate, then they should do the same.

CREATE TABLE User (
  id int PRIMARY KEY AUTO_INCREMENT
   -- ... stuff ...
  latitude double,
  longitude double
);

Assuming u→u′ is 1:0..1

If Coordinates are optional for Users, that is, some Users don't have Coordinates, then you should split coordinates to a separate table, but maintaining the primary key. Again, the same is true for other entities that have Coordinates. You might also pick this option even if Coordinates are 1:1..1, but for some reason the lookup on Coordinate is expensive. (This is sometimes true for attributes with a long, variable-length text field, for example, where moving those attributes to a separate child table improves performance in the parent table.)

CREATE TABLE User (
    id int PRIMARY KEY AUTO_INCREMENT
     -- ... stuff ...
);

CREATE TABLE UserCoordinate (
    id int PRIMARY KEY
    latitude double,
    longitude double,
    FOREIGN KEY ( id ) REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE
);

You complained that this will create multiple tables, such as UserCoodinate, CompanyCoordinate, AssetCoordinate, ShippingCoordinate, etc., etc. Yes, it's true. You called it "bloated", a term I disagree with. Metadata has a purpose; let it do its job.

Many of my university students say the same as you when I teach this topic. However, this is due to their lack of understanding and experience with relational design. If you don't want a normalized data structure, that's your decision, but SQL and ORM libraries are designed to facilitate relational design, so don't try to wrest them to cover the flaws caused by your design decisions. As you'll see with a later example, the number of tables can grow even more. This is normal, and part of using relational data structures. Another option is to try something schema-less, like Couchbase. A word of warning: it appears you have very structured data, so a relational structure is likely your best option.

Assuming u→u′ is 1:0..n OR 1:1..n

In this scenario, Coordinates are optional (or not), and a User can have one or many of them. You probably know this solution. I suspect it's not the case with your data, but I'll cover it for completeness.

CREATE TABLE User (
    id int PRIMARY KEY AUTO_INCREMENT
     -- ... stuff ...
);

CREATE TABLE UserCoordinate (
    id int PRIMARY KEY
    user_id int REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE
    latitude double,
    longitude double
);

As you can see, there are three different design possibilities depending on the characteristics of your data. Let's now example the third question ("Are values duplicated between u′ c′?"), as this has implications for our design.

Different entity instances (e.g., two different Users) may have the same coordinates

In other words, the relationship between u and u′ is n:n.** This requires User and Coordinate to be in separate tables, with a table called an "associative entity" or "association table" between them.

CREATE TABLE User (
    id int PRIMARY KEY AUTO_INCREMENT
     -- ... stuff ...
);

CREATE TABLE Coordinate (
    id int PRIMARY KEY
    latitude double,
    longitude double
);

CREATE TABLE UserCoordinate (
    user_id int REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE,
    coord_id int REFERENCES Coordinate(id) ON DELETE CASCADE ON UPDATE CASCADE
);

What if different entities (e.g., Users and Companies) have the same coordinates (i.e., values are duplicated between u′, c′, et al.)

Let's start with the assumptions of the above example. If followed to the letter, this would suggest there is a Coordinate table for Users (perhaps called 'UCoordinate'), one for Companies (perhaps called 'CCoordinate'), one for Assets (etc., etc.,). A designer could be forgiven for looking at that and thinking the design is messy. Especially if there were duplicate values between the tables. What if there were only one Coordinate table?

If each User (or Company, or Asset, etc.) has only one (non-optional) and not more than one coordinate, normalization would dictate the following:

CREATE TABLE Coordinate (
    id int PRIMARY KEY
    latitude double,
    longitude double
);

CREATE TABLE User (
    id int PRIMARY KEY AUTO_INCREMENT -- uses *same* PK as Coordinate
     -- ... stuff ...
    FOREIGN KEY ( id ) REFERENCES Coordinate(id) ON DELETE SET NULL ON UPDATE SET CASCADE
);

CREATE TABLE Company (
    id int PRIMARY KEY AUTO_INCREMENT -- uses *same* PK as Coordinate
     -- ... stuff ...
   FOREIGN KEY ( id ) REFERENCES Coordinate(id) ON DELETE SET NULL ON UPDATE SET CASCADE
);

In this simple set up, multiple Users, Companies, or Assets could reference the same coordinate. However, it's not possible when starting with a coordinate to do a simple query to discover whether it's used by any other entity. And perhaps this is a necessary use case for your application. Bite the bullet, and write the complex business logic in your code.

If each User (or Company, or Asset, etc.) has only 0..1 (optional) or 0..n (many) coordinates, the following design should be used.

Note the repeated use of the Coordinate's primary key in its specialized tables.

CREATE TABLE User (
    id int PRIMARY KEY AUTO_INCREMENT
     -- ... stuff ...
);

CREATE TABLE Company (
    id int PRIMARY KEY AUTO_INCREMENT
     -- ... stuff ...
);

CREATE TABLE Coordinate (
    id int PRIMARY KEY
    latitude double,
    longitude double
);

CREATE TABLE UserCoordinate (
    user_id int REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE,
    coord_id int REFERENCES Coordinate(id) ON DELETE CASCADE ON UPDATE CASCADE,
    -- Most ORMs require you also give this table its own `id` primary key,
    -- whereas relational theory says to use (user_id, coord_id) as a composite primary key.
);

CREATE TABLE CompanyCoordinate (
    company_id int REFERENCES Company(id) ON DELETE CASCADE ON UPDATE CASCADE,
    coord_id int REFERENCES Coordinate(id) ON DELETE CASCADE ON UPDATE CASCADE,
    -- Most ORMs require you also give this table its own `id` primary key,
    -- whereas relational theory says to use (user_id, coord_id) as a composite primary key.
);

This method creates even more tables, but is the proper relational way of modeling these relationships. I promise. Doing it this way will prevent many future problems.

Don't ever do it this way

CREATE TABLE User (
    id int PRIMARY KEY AUTO_INCREMENT
     -- ... stuff ...
);

CREATE TABLE Company (
    id int PRIMARY KEY AUTO_INCREMENT
     -- ... stuff ...
);

CREATE TABLE Coordinate (
    id int PRIMARY KEY, 
    user_id int REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE,
    coord_id int REFERENCES Coordinate(id) ON DELETE CASCADE ON UPDATE CASCADE,
    -- etc., etc., where these FKs are NULLable
    latitude double,
    longitude double
);

Just don't. It's wrong. In the classroom, a submission like this would get a failing grade. In business, I'd ask my developer to do it again, and seriously consider retraining or replacing the employee. If your inclination is to do it this way, go read Karwin's SQL Antipatterns.

Conclusion

As you can see, the underlying assumptions of your data greatly influence the design of your tables, and thus the answer you'd receive. Yet, you didn't share any of these assumptions, or your table design, and still expected an answer, writing:

i questioning on the context of this library not questioning on general relational database design issue. I cannot say that u already have experience using that flexible foreign key database design...

The short answer, fix your design, and then the examples in the Paris documentation work just fine. Even if you choose to use the anti-pattern, @treffynnon's answer above is the same one I'd give you (use the where_equal() method and handle your weird joins yourself):

$coordinates1 = ORM::for_table('coordinate')
     ->where_equal('table_reference', 'user')
     ->where_equal('table_id', 20)
     ->find_many();
$coordinates2 = ORM::for_table('coordinate')
     ->where_equal('table_reference', 'company')
     ->where_equal('table_id', 20)
     ->find_many();

A note on English

You perhaps didn't intend it, but your English was a bit rude. Particularly this part (emphasis mine):

I cannot say that u already have experience using that flexible foreign key database design... let me say there is flaw that i known on your short answer solution.

I'll overlook these, on the belief that it's a problem with translation and culture. But please be more considerate in the future.

However, there are some simple things that you should always do when asking someone you don't know for help, especially if you want to be taken seriously.

1. Use "because" instead of "coz"
2. Use "you" instead of "u"
3. Capitalize the first letter of each sentence.

These simple steps will go a long way toward people taking your question more seriously.

This is not a place for support

I've given your question much more attention than it deserves. The "Issues" tab is not a place for support requests or questions about how to use the library. Use StackOverflow for that. Seriously. It's what that community is designed for, and there are many people there willing to volunteer their time. If you have a Bug or Feature Request, then open an issue here. But please make sure you understand how to use the library first. (Your question was neither a bug nor a feature request. That's why it was closed. Please don't expect it to be reopened.)

The Final Word

Research has repeatedly shown that most bugs and project failures are caused by problems in the design phase. Be willing to change your design to fit your tools, and you'll be happier in the long run.

[navotera]

Great explanation i believe it tooks time to read and understand this. Allow me to say sorry if my language seem not appropriate.

Hope your explanation can help others too

On Fri, Aug 11, 2017, 04:09 Tom G notifications@github.com wrote:

Let's start at the top: You were advised to use StackOverflow (etc.) for support. Please do that, rather than make demands of @treffynnon https://github.com/treffynnon, or continue this thread.

Secondly: "Flexible Foreign Key" is a database design anti-pattern. Doing it that way will cause you problems down the road. (Chapters 7 & 8 of Karwin's SQL Antipatterns https://pragprog.com/book/bksqla/sql-antipatterns are excellent and highly recommended.)

Part of the difficulty in helping you is that you haven't provided enough information. Another difficulty is that you've received a couple of helpful responses, but aren't listening to the answer that the issue is with your design and not with Paris.

Allow me to expound.

Let's assume for a moment that you have a User table, which I'll call u, and a Company table which I'll call c. Both have Coordinates that you'd also like to model in your database, which for the moment I'll call u′ and c′ respectively. However, the best way to model this depends on the answers to three questions:

1. What is the minimal multiplicity1 of the relationship between u and u′? (And assuming it's the same answer, between c and c′?)
2. What is the maximum multiplicity between u and u′?
3. Are values duplicated between u′ c′? In other words, are Users and Companies likely to have the same coordinates?

1: I'll use the term 'multiplicity' instead of 'cardinality'. The former is a UML term, and the latter is a relational modeling term. Some disagree on which should be used https://stackoverflow.com/questions/17877582/multiplicity-vs-cardinality but mostly they mean the same thing. Assuming u→u′ is 1:1..1

If all Users have one and only one Coordinate, then Coordinate should be attributes of the User table. If the same relationship between other entities and their Coordinate, then they should do the same.

CREATE TABLE User ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... latitude double, longitude double );

Assuming u→u′ is 1:0..1

If Coordinates are optional for Users, that is, some Users don't have Coordinates, then you should split coordinates to a separate table, but maintaining the primary key. Again, the same is true for other entities that have Coordinates. You might also pick this option even if Coordinates are 1:1..1, but for some reason the lookup on Coordinate is expensive. (This is sometimes true for attributes with a long, variable-length text field, for example, where moving those attributes to a separate child table improves performance in the parent table.) [image: screen shot 2017-08-10 at 3 59 48 pm] https://user-images.githubusercontent.com/9618/29189564-04c3c65c-7de5-11e7-9651-f82902030b4f.png

CREATE TABLE User ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... ); CREATE TABLE UserCoordinate ( id int PRIMARY KEY latitude double, longitude double, FOREIGN KEY ( id ) REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE );

You complained that this will create multiple tables, such as UserCoodinate, CompanyCoordinate, AssetCoordinate, ShippingCoordinate, etc., etc. Yes, it's true. You called it "bloated", a term I disagree with. Metadata has a purpose; let it do its job.

Many of my university students say the same as you when I teach this topic. However, this is due to their lack of understanding and experience with relational design. If you don't want a normalized data structure, that's your decision, but SQL and ORM libraries are designed to facilitate relational design, so don't try to wrest them to cover the flaws caused by your design decisions. As you'll see with a later example, the number of tables can grow even more. This is normal, and part of using relational data structures. Another option is to try something schema-less, like Couchbase. A word of warning: it appears you have very structured data, so a relational structure is likely your best option. Assuming u→u′ is 1:0..n OR 1:1..n

In this scenario, Coordinates are optional (or not), and a User can have one or many of them. You probably know this solution. I suspect it's not the case with your data, but I'll cover it for completeness.

CREATE TABLE User ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... ); CREATE TABLE UserCoordinate ( id int PRIMARY KEY user_id int REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE latitude double, longitude double );

As you can see, there are three different design possibilities depending on the characteristics of your data. Let's now example the third question ("Are values duplicated between u′ c′?"), as this has implications for our design. Different entity instances (e.g., two different Users) may have the same coordinates

In other words, the relationship between u and u′ is n:n.** This requires User and Coordinate to be in separate tables, with a table called an "associative entity" or "association table" between them.

CREATE TABLE User ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... ); CREATE TABLE Coordinate ( id int PRIMARY KEY latitude double, longitude double ); CREATE TABLE UserCoordinate ( user_id int REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE, coord_id int REFERENCES Coordinate(id) ON DELETE CASCADE ON UPDATE CASCADE );

What if different entities (e.g., Users and Companies) have the same coordinates (i.e., values are duplicated between u′, c′, et al.)

Let's start with the assumptions of the above example. If followed to the letter, this would suggest there is a Coordinate table for Users (perhaps called 'UCoordinate'), one for Companies (perhaps called 'CCoordinate'), one for Assets (etc., etc.,). A designer could be forgiven for looking at that and thinking the design is messy. Especially if there were duplicate values between the tables. What if there were only one Coordinate table? If each User (or Company, or Asset, etc.) has only one (non-optional) and not more than one coordinate, normalization would dictate the following:

[image: screen shot 2017-08-10 at 4 05 08 pm] https://user-images.githubusercontent.com/9618/29189804-e044e030-7de5-11e7-813c-d19053d20568.png

CREATE TABLE User ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... coord_id int FOREIGN KEY ( id ) REFERENCES Coordinate(id) ON DELETE SET NULL ON UPDATE SET CASCADE ); CREATE TABLE Coordinate ( id int PRIMARY KEY latitude double, longitude double ); CREATE TABLE Company ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... coord_id int FOREIGN KEY ( id ) REFERENCES Coordinate(id) ON DELETE SET NULL ON UPDATE SET CASCADE );

In this simple set up, multiple Users, Companies, or Assets could reference the same coordinate. However, it's not possible when starting with a coordinate to do a simple query to discover whether it's used by any other entity. And perhaps this is a necessary use case for your application. Bite the bullet, and write the complex business logic in your code. If each User (or Company, or Asset, etc.) has only 0..1 (optional) or 0..n (many) coordinates, the following design should be used.

Note the repeated use of the Coordinate's primary key in its specialized tables.

CREATE TABLE User ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... ); CREATE TABLE Company ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... ); CREATE TABLE Coordinate ( id int PRIMARY KEY latitude double, longitude double ); CREATE TABLE UserCoordinate ( user_id int REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE, coord_id int REFERENCES Coordinate(id) ON DELETE CASCADE ON UPDATE CASCADE, -- Most ORMs require you also give this table its own id primary key, -- whereas relational theory says to use (user_id, coord_id) as a composite primary key. ); CREATE TABLE CompanyCoordinate ( company_id int REFERENCES Company(id) ON DELETE CASCADE ON UPDATE CASCADE, coord_id int REFERENCES Coordinate(id) ON DELETE CASCADE ON UPDATE CASCADE, -- Most ORMs require you also give this table its own id primary key, -- whereas relational theory says to use (user_id, coord_id) as a composite primary key. );

This method creates even more tables, but is the proper relational way of modeling these relationships. I promise. Doing it this way will prevent many future problems. Don't ever do it this way

CREATE TABLE User ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... ); CREATE TABLE Company ( id int PRIMARY KEY AUTO_INCREMENT -- ... stuff ... ); CREATE TABLE Coordinate ( id int PRIMARY KEY, user_id int REFERENCES User(id) ON DELETE CASCADE ON UPDATE CASCADE, coord_id int REFERENCES Coordinate(id) ON DELETE CASCADE ON UPDATE CASCADE, -- etc., etc., where these FKs are NULLable latitude double, longitude double );

Just don't. It's wrong. In the classroom, a submission like this would get a failing grade. In business, I'd ask my developer to do it again, and seriously consider retraining or replacing the employee. If your inclination is to do it this way, go read Karwin's SQL Antipatterns https://pragprog.com/book/bksqla/sql-antipatterns. Conclusion

As you can see, the underlying assumptions of your data greatly influence the design of your tables, and thus the answer you'd receive. Yet, you didn't share any of these assumptions, or your table design, and still expected an answer, writing:

i questioning on the context of this library not questioning on general relational database design issue. I cannot say that u already have experience using that flexible foreign key database design...

The short answer, fix your design, and then the examples in the Paris documentation http://paris.readthedocs.io/en/latest/ work just fine. Even if you choose to use the anti-pattern, @treffynnon https://github.com/treffynnon's answer above is the same one I'd give you (use the where_equal() method and handle your weird joins yourself):

$coordinates1 = ORM::for_table('coordinate') ->where_equal('table_reference', 'user') ->where_equal('table_id', 20) ->find_many();$coordinates2 = ORM::for_table('coordinate') ->where_equal('table_reference', 'company') ->where_equal('table_id', 20) ->find_many();

A note on English

You perhaps didn't intend it, but your English was a bit rude. Particularly this part (emphasis mine):

I cannot say that u already have experience using that flexible foreign key database design... let me say there is flaw that i known on your short answer solution.

I'll overlook these, on the belief that it's a problem with translation and culture. But please be more considerate in the future.

However, there are some simple things that you should always do when asking someone you don't know for help, especially if you want to be taken seriously.

1. Use "because" instead of "coz"
2. Use "you" instead of "u"
3. Capitalize the first letter of each sentence.

These simple steps will go a long way toward people taking your question more seriously. This is not a place for support

I've given your question much more attention than it deserves. The "Issues" tab is not a place for support requests or questions about how to use the library. Use StackOverflow http://stackoverflow.com for that. Seriously. It's what that community is designed for, and there are many people there willing to volunteer their time. If you have a Bug or Feature Request, then open an issue here. But please make sure you understand how to use the library first. (Your question was neither a bug nor a feature request. That's why it was closed. Please don't expect it to be reopened.) The Final Word

Research has repeatedly shown that most bugs and project failures are caused by problems in the design phase. Be willing to change your design to fit your tools, and you'll be happier in the long run.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/j4mie/paris/issues/131#issuecomment-321659843, or mute the thread https://github.com/notifications/unsubscribe-auth/ABW900046k-M3xkk-qkvdZAxbCluq-Txks5sW2OEgaJpZM4Oxp90 .