0

This is the current structure of the database

Business login

A customer would be anyone in a predetermined list of customers that each company has information about. A profile is a login account they can use to access thier customer information.

When someone logs into their profile they can add one or more customer accounts to their profile as long as they have valid information about the customer (name,...)

It's not all that common but sometimes a customer will belong to multiple profiles.

customer :

+------------------+--------------------------+-----------+------------+-----+
| id (primary key) | company_id (primary key) | firstname | lastname   | ... |
+------------------+--------------------------+-----------+------------+-----+
|               1  |                        1 | John      | Doe        | ... |
+----------------------------------------------------------------------------+
|               2  |                        1 | Jane      | Doe        | ... |
+----------------------------------------------------------------------------+
|               3  |                        2 | Elmo      | Mack       | ... |
+----------------------------------------------------------------------------+
|               1  |                        2 | Chester   | Torain     | ... |
+----------------------------------------------------------------------------+

profile :

+------------------+--------------------------+-----------+-------------+-----+
| id (primary key) | company_id (primary key) | username  | email       | ... |
+------------------+--------------------------+-----------+-------------+-----+
|               1  |                        1 | odoe      | j@gmail.com | ... |
+-----------------------------------------------------------------------------+
|               2  |                        1 | adoe      | d@gmail.com | ... |
+-----------------------------------------------------------------------------+
|               3  |                        2 | emlo      | e@gmail.com | ... |
+-----------------------------------------------------------------------------+
|               1  |                        2 | ches      | c@gmail.com | ... |
+-----------------------------------------------------------------------------+

(pivot) customer_profile :

+------------------+-------------+-------------+
| id (primary key) | customer_id | profile_id  |
+------------------+-------------+-------------+
|               1  |          1  |           1 |
+----------------------------------------------+
|               2  |          1  |           2 |
+----------------------------------------------+
|               3  |          3  |           3 |
+----------------------------------------------+

There is a many to many relationship between customer and profile. A customer can have multiple profiles and each profile can be used to access one or many customers, hence the pivot table.

Both the customer and profile tables have composite keys of (id and company_id) that keep the records unique because of the way the data comes in.

There is also a Company Table

company :

+------------------+---------------+------+
| id (primary key) | company_name  | ...  |
+------------------+---------------+------+
|               1  |        google | ...  |
+-----------------------------------------+
|               2  |         yahoo |  ... |
+-----------------------------------------+

The company table has a one to many relation with the customer table and a one to many relationship with the profile table.

As a sidenote there are more tables in the database all with the same composite key structure.

Problem

If I do a normal query

SELECT
    c.`firstname`
,   c.`lastname`
,   p.`username`
,   p.`email`
FROM
    `customer` c
LEFT JOIN `customer_profile` cp ON (cp.`customer_id` = c.`id`)
LEFT JOIN `profile` p ON  (p.`id` = cp.`profile_id`)
WHERE  c.`id` = 1 AND c.`company_id` = 1;

I would end up with incorrect relations where customer 1 company 1 (John Doe) would have records for his profile and for customer 1 company 2 (Chesters) profile because the pivot table doesn't account for the company id

The desired result would be the (firstname,lastname) of the customer and the (username,and email) of all profiles associated with that exact customer.

This is what the desired result would look be givin this data:

+-------------+------------+-----------+--------------+
| firstname   | lastname   | username  | email        |
+-------------+------------+-----------+--------------+
| John        | Doe        | odoe      | j@gmail.com  |
+-----------------------------------------------------+
| John        | Doe        | adoe      | d@gmail.com  |
+-----------------------------------------------------+

The actual result could contain additional records where the pivot table adds a relationship that shouldn't be.

But the actual result would also contain:

+-------------+------------+-----------+--------------+
| firstname   | lastname   | username  | email        |
+-------------+------------+-----------+--------------+
| John        | Doe        | odoe      | j@gmail.com  |
+-----------------------------------------------------+
| John        | Doe        | adoe      | d@gmail.com  |
+-----------------------------------------------------+
| John        | Doe        | ches      | c@gmail.com  |  <--
+-----------------------------------------------------+

I would need to filter out any records like this where the profile and customer belong to different companies.

To fix this I have tried this query

SELECT
    c.`firstname`
,   c.`lastname`
,   p.`username`
,   p.`email`
FROM
    `customer` c
LEFT JOIN `customer_profile` cp ON (cp.`customer_id` = c.`id`)
LEFT JOIN `profile` p ON  (c.`company_id` = p.`company_id` AND p.`id` = cp.`profile_id`)
WHERE  c.`id` = 1 AND c.`company_id` = 1;

which does give be the desired results because it joins by the company_id key as well This is what we are currently using I can't tell if it will work for all situations but it hasn't caused any unexpected results yet.

Question

My main problem is that I'm lost for what to do about the company table. If instead of what I currently have would joining the company table to the customer table be enough to remove any undesired records?

Something like this?

SELECT
    c.`firstname`
,   c.`lastname`
,   p.`username`
,   p.`email`
FROM
    `company` co
LEFT JOIN `customer` c ON co.`id` = c.`company_id`
LEFT JOIN `customer_profile` cp ON (cp.`customer_id` = c.`id`)
LEFT JOIN `profile` p ON p.`id` = cp.`profile_id`
WHERE c.`id` = 1 AND c.company_id = 1;

It doesn't seem like it would because the pivot table doesn't have the company_id record.

Also any help finding another situation like mine that I can reference or take notes from would be very much appreciated.

Edit

Changed the queries where clause to search by customer.id instead of by firstname and changed the ' to ` This is how the wuery was supposed to be.

If the problem is in fact with the design of the database that's fine I just need to know. It would be simpler to use the current design but if that's where the problem is than that's that. From what I can tell from "philipxy" comment the problem is that there is no company_id field on the pivot table.

Edit

The logic behind the query is this

When this customer at this company (c.id AND c.company_id) has profiles with that company get the name and profile details.

nickc
  • 1,193
  • 1
  • 6
  • 15
  • Why do you need a `company_id` in the `profile` table? There's already a `company_id` column in the `customer` table, putting it in their profile is redundant. – Barmar Oct 23 '18 at 19:42
  • 1
    [so] is not a good site for asking general design questions like this. It's for questions about problems you're having in code you've written. – Barmar Oct 23 '18 at 19:46
  • The company_id field is part of the key in most tables. I'm not sure exactly what to call it but to give it a term I would call it a global field. Data is added to the tables externally by company and because of this just the `profile_id` or the `customer_id` doens't necessarily point to a unique record. That's why the company_id is in most tables, because the `company_id` combined with the tables id key creates the full primary key – nickc Oct 23 '18 at 19:47
  • The `id` column should be the primary key of each table. You don't need another unique key, you relate them to companies with a pivot table. – Barmar Oct 23 '18 at 20:09
  • Hi. It's not clear what your design is & what columns are where & what constraints hold so please give a [mcve]. Even so it's not clear what an answer would be because explaining "best practice" (actually, here, "straightforward practice") is what a textbook is for & not a SO answer & you should read one & ask a question where you get stuck following it. PS Clarify via post edits, not comments. – philipxy Oct 24 '18 at 01:02
  • 1
    Please give DDL. Please use normal quotation--you have reversed ' & ". For each base table give what a row in it says about the business situation--until then we can't know what rows any SQL query is asking for in business terms. Also give that for your desired result--otherwise we can't know a correct query or why yours is wrong for your desired result. [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) – philipxy Oct 24 '18 at 20:25
  • PS "John Doe would have records for his ..."? That name doesn't identify a customer. It is a name used by some customers at some companies. Moreover a different name could identity the same person, so another result row could be about the same customer. So--give for each base table the statement template (*(characteristic) predicate*) that each row in it makes into a statement (*proposition*) true of the business situation. – philipxy Oct 24 '18 at 20:42
  • customer-company & profile-company are M:M. What rows go in Customer_profile?--customers & their profiles? What is id?--a surrogate? If so there's no data on the profile of a customer at a company. You maybe want to query for that but you can't in this DB. It's hard to guess from your queries what you want because you don't say what rows the queries are asking for in business terms or base tables & the queries are maybe wrong. (It would probably help you find where you went wrong in design if you tried to give the justification for this design per your business & textbook's design method.) – philipxy Oct 25 '18 at 03:14
  • 1
    The "desired" description is unclear--it doesn't give the layout of the customer data in the result. The wrong code & output exampes & their "because"s are not very helpful, because they're wrong, and we can see what the code does. Look: I have a row here--(firstname, lastname, username, email). Under what condition does it go in the correct output, in terms of input tables and/or the business situation? Finish/fix this statement: "When some row (c.id, c.firstname, ...) is in Customer & c.firstname=firstname & c.id=1 & ...". Or this one: "When customer 1 has some first name c.firstname & ...". – philipxy Oct 25 '18 at 14:14
  • I fixed the queries and this time checked them with the mockup data I gave. But I'm having a little bit of a rough time spelling out exactly the business logic because I didn't write the query originally. I did add to my question how I understand it – nickc Oct 25 '18 at 15:37
  • You still don't say what output is given input. You just give jumbles of words & phrases that have something to do with it. Please try to phrase per my comments. Rows where "some person is named [firstname] [lastname] at some company & uses some profile with [username] & [email] there"? What even of customer_profile? Rows where "[id] ids the fact that some customer [customer_id] at some company uses profile [profile_id] there"? "... at all their companies"?) Are there constraints? "profiles at a company have different user names"? Time for a textbook on information modeling & database design. – philipxy Oct 26 '18 at 23:57
  • PS Please don't add EDITs; just edit a post to be the best presentation possible. PS Google 'stackexchange comment notifications' re using `@` in a comment to notify one non-poster commenter that you made the comment. – philipxy Oct 27 '18 at 00:03

1 Answers1

0

Ultimately I think my problem stemmed from a bad database design. I ended up adding a new unique primary key to each table

So like this on the customer table:

+-----+-------------+------------------------+-------------+-----+
| id  | customer_id | company_id | username  | email       | ... |
+-----+-------------+------------+-----------+-------------+-----+
|  1  |           1 |          1 | odoe      | j@gmail.com | ... |
+----------------------------------------------------------------+
|  2  |           2 |          1 | adoe      | d@gmail.com | ... |
+----------------------------------------------------------------+
|  3  |           3 |          2 | emlo      | e@gmail.com | ... |
+----------------------------------------------------------------+
|  4  |           1 |          2 | ches      | c@gmail.com | ... |
+----------------------------------------------------------------+

This solved the problem of the pivot table matching records that shouldn't because each customer record has a unique key that makes the pivot table a normal many to many relationship.

nickc
  • 1,193
  • 1
  • 6
  • 15