Is my ERD Model Correct

[CementCarver]

When I designed my erd diagram for this project I'm working on, I based my design that the master MEMBER_ID column from my main business table would link to either of three related tables. So, I've updated this MEMBER_ID number to the other tables where the company names matched. Once you select the company name you're looking for, the record displayed on the main form can either be linked to one, two or all three of the related tables.



But it seems odd that when I select certain company names, they won't populate the main form. Only those records that share the same id number and which are found in all three tables, does the record then appear properly.

I'm thinking that my erd relationships may be the cause of this. Can anyone help? Attached is my erd diagram and the sql statement that drives the main form.

SELECT BUSINESS.*, FULLGROWER.Active_InActive, FULLGROWER.Out_of_Business, FULLGROWER.Farm_No, FULLGROWER.District AS District_FULLGROWER, FULL_MARKETER.[Business Name], FULLPACKER.[Business Name]
FROM ((BUSINESS INNER JOIN FULL_MARKETER ON BUSINESS.MEMBER_ID = FULL_MARKETER.MEMBER_ID) INNER JOIN FULLGROWER ON BUSINESS.MEMBER_ID = FULLGROWER.MEMBER_ID) INNER JOIN FULLPACKER ON BUSINESS.MEMBER_ID = FULLPACKER.MEMBER_ID;



CementCarver

[ssanfu]

Did you mean to have a one-to-one relationship between:

Business <-> FullPacker

Business <-> Full_Marketer

I would think you would want these to be one to many relationships.



I always have the table PK field as the first field.
Any FK fields are next.

And each table has a different name for the PK. (it is confusing reading SQL when every PK field is the same name)
For the table "Business", the PK field could be "Business_ID"
For Full_Marketer, the PK field name could be "FullMarketer_ID". If this field would be a FK field in another table, I would use "FullMarketer_FK"


I don't remember ever using a 1 to 1 relationship.......

[CementCarver]

Thanks Ssanfu for your return comments. You're right, my naming conventions for PK used is confusing. But as I designed the database, the relationships were as thus:

1. There should only be one record related between BUSINESS and FULLPACKER
2. The same goes for BUSINESS and FULLMARKETER
3. And the same goes for BUSINESS and FULLGROWER

So, there can be multiple variations in my db: A business record can be related to be a :

1.FULLGROWER, FULLPACKER and FULLMARKETER
2.FULLGROWER and FULLPACKER
3.FULLGROWER
4.FULLPACKER and FULLMARKETER
5.FULLPACKER
6.FULLMARKETER and FULLGROWER
7.FULLMARKETER

That's the relationship in my ERD diagram that I tried to establish.

CementCarver

[ssanfu]

Still thinking........ ( ouch, that hurts)

[ssanfu]

What I'm having a problem is that the table structure is not normalized.

But it seems odd that when I select certain company names, they won't populate the main form.

That is the way it works in a relational database.

Only those records that share the same id number and which are found in all three tables, does the record then appear properly.

You answered your own question..

Look at this part of your query:

FROM ((BUSINESS INNER JOIN FULL_MARKETER ON BUSINESS.MEMBER_ID =  FULL_MARKETER.MEMBER_ID) 
INNER JOIN FULLGROWER ON BUSINESS.MEMBER_ID =  FULLGROWER.MEMBER_ID) 
INNER JOIN FULLPACKER ON BUSINESS.MEMBER_ID =  FULLPACKER.MEMBER_ID;

Like you said, if one table doesn't have a record, that company name will not be selected. There has to be a matching record in each of the tables to be selected.


You have a lot of fields that are duplicated in many tables. For instance:

BUSINESS.Business_Name
FULLGROWER.[Business Name]
FULLPACKER.[Business Name]
FULL_MARKETER.[Business Name]
FULLGROWER_SQFT_BILLED.BusinessName
FULL_COMM_BREAKDOWN.[Business Name]
GROWER_MARKETER_SQFT_BILLED.[Business Name]
GROWER_TRACEABILITY.[Business Name]
--
BUSINESS.GROWER_Name_FIXED
FULLPACKER.Grower
FULL_MARKETER.Grower
--
FULLPACKER.District
FULL_MARKETER.District


------------------------------------
There are a lot of fields I can't see and I don't know squat about what your dB does, but (IMO) this structure still needs some work to be normalized. In the past, I have made dBs that were not normalized and I paid for it... major headaches to create queries or reports.
Designing normalized table structures is an art and a science. There are many people on this forum that are wizards at designing normalized table structures...... sadly I am not one of them. I still have to work long and hard - which is why I use whiteboards to start designing.


Anyway, this is my $0.02 worth (I used to be worth $0.35)

[CementCarver]

Thanks for your return comments Ssanfu,

I'm confused to say the least. But all I had at the beginning were matching company names between BUSINESS, FULLGROWER, FULLPACKER and FULLMARKETER. So I've used the business name too often in my tables and thus they appear not normalized. So if I was change the statement below, to allow for any match to the three related tables, first, second or third, then how would you restructure the SQL statement to allow this?


SELECT BUSINESS.*, FULLGROWER.Active_InActive, FULLGROWER.Out_of_Business, FULLGROWER.Farm_No, FULLGROWER.District AS District_FULLGROWER, FULL_MARKETER.[Business Name], FULLPACKER.[Business Name]
FROM ((BUSINESS INNER JOIN FULL_MARKETER ON BUSINESS.MEMBER_ID = FULL_MARKETER.MEMBER_ID) INNER JOIN FULLGROWER ON BUSINESS.MEMBER_ID = FULLGROWER.MEMBER_ID) INNER JOIN FULLPACKER ON BUSINESS.MEMBER_ID = FULLPACKER.MEMBER_ID;

CementCarver

[ssanfu]

You'd have to explain what you are modeling. I know nothing about your process.

I would really suggest first creating normalized tables/relationships.

I don't understand your relationships between the 4 tables.

ONE .................. MANY
-----------------------
BUSINESS ..........FULLGROWER


Why are these 1-to-1?
ONE ................... ONE
-----------------------
BUSINESS ..........FULLPACKER
BUSINESS ..........FULLMARKETER



But in answer to your question, try this:

SELECT BUSINESS.*, FULLGROWER.Active_InActive, FULLGROWER.Out_of_Business, FULLGROWER.Farm_No, FULLGROWER.District AS District_FULLGROWER, FULL_MARKETER.[Business Name], FULLPACKER.[Business Name]
FROM ((BUSINESS LEFT JOIN FULL_MARKETER ON BUSINESS.Member_ID = FULL_MARKETER.MEMBER_ID) INNER JOIN FULLGROWER ON BUSINESS.Member_ID = FULLGROWER.MEMBER_ID) LEFT JOIN FULLPACKER ON BUSINESS.Member_ID = FULLPACKER.MEMBER_ID;

[CementCarver]

Thanks Ssanfu, you're right though, the relationship between BUSINESS and FULLGROWER should be one-to-one. I'll try your sql suggestion.

CementCarver