Software

Ask something
Discussion Locked

Many to one with null table?

0 helpful
5 replies

by natasha.epperson

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Okay I've created a relationship on many to one and many to one on 3 tables. The issue i'm running into is if table 3, there isn't any data to enter or table 2 needs no data but i have data to put in table 3. I need to be able to not enter data in table 2 vs 3 and so forth. So like Table 1 is company, table 2 is address table 3 is phone numbers. I'll always of course know table 1's info. But I won't always know address or fax. and in this company profile there will be many fax numbers to 1 address but I won't always know that. What's happening when I put into form or try in query it will not generate auto- primary key unless I enter information into the table that has no information. Do I need to just make an unknown list box? I hate doing that, that would cause such redundant data. Or is there another way?

5 replies

by tony hopkinson

but these might help.
Company table (CompanyID, .....)
Address Table (CompanyID, AdressID, ....
Phone Table (CompanyID, PhoneID, ....
So in both Address and Phone, CompanyID is a foreign key. And the keys in this table are compound CompanyID and an other.

The other regular scheme is to use a pivot table
So Tables for company, Address and Phone,
then pivot tables CompanyAddresess,CompanyPhones and AdressPhones.
This design is much more flexible and modular, to add say InvoiceAddresses, all you need is another pivot table and then a function that adds the companyid and an existing adress id (or allows you to create a new one). Once you've done the first one it's cut&paste and search&replace.

Note these schemes are far easier to implement if you separate out the facilities for maintaining Addresses and Phones from the entities that use addresses and phones.

by natasha.epperson

okay let me be more specific.
I have company table:
CompanyName(primary Key)
Than I have Address table
AddressID(primary key), CompanyID (foreign key)
(the address table is a many to one relation to the company table)
Now I have a phone number table tied to the address-
AddressID (foreign key)- no primary key. This is tied as a many to one relation to the address for there can be more than one number per address...

The issue I'm running into is when I'm entering data in a form and say I don't have the address information but I know the fax information. It gives me problem where tie of keys is lost... ie it will save the data but won't tie it to the address/company. Or visa versa if i know the address but not the fax number I get error you can not change or add as this table is required in table bla bla.

by tony hopkinson

have only provisioned for company to address to phone, but you want
Company to Address
Company to Phone
Address To Phone

So the second scheme I detailed should suit your needs. With your layout you can't enter a phone with out an address, so you'd have to create a dummy record with no details.

My Solution wiould be
Company Table with a primary key
Address Table with a primary key
Phone Table with a primary ket then
Then three pivot tables which will give you many to many all round, if you only need many to one in the case of company phone, then don't bother with the CompanyPhone Pivot table)
CompanyPhone(CompanyID, PhoneID)
CompanyAddress(CompanyID,AddressID)
AddressPhone(AddressID,PhoneID)
You can do practically anything you want with this, a Company with a phone but no address
Company with a phone and 1 to many addresses some of which may have no phone or even the same phone.
Even two companies with the same adress and or phone.

Your business application logic will be a subset of the potential relationships allowed by the database design.

On top of that it's a simple matter to add other things such as emails and urls and best of all no nulls in any of your relationships.

by natasha.epperson

okay I did what you said to do, but now can't get query to work right.
Query sql looks like this:
SELECT tbl_address.addressID, tbl_address.companyID, tbl_address.StreetAddress, tbl_address.City, tbl_address.State, tbl_address.Zip, tbl_company.companyName, tbl_company.phoneID, tbl_company.addressID, tbl_company.regionID, tbl_company.commodity, tbl_phone.phoneID, tbl_phone.companyID, tbl_phone.contactName, tbl_phone.faxPhone, tbl_phone.workPhone, tbl_phone.cellPhone, tbl_phone.eMail
FROM (tbl_address INNER JOIN tbl_company ON (tbl_company.companyName = tbl_address.companyID) AND (tbl_address.addressID = tbl_company.addressID)) INNER JOIN tbl_phone ON (tbl_phone.phoneID = tbl_company.phoneID) AND (tbl_company.companyName = tbl_phone.companyID);

by tony hopkinson

I suggested
You need to select all the adressses from tbl_address where the addressid is in tbl_CompanyAddress and the companyId in tbl_CompanyAddress = ??
If you want the phones as well then you need to join address to phone via addressphones and it will have to be outer join otherwise you'll only get addresses with phones.

Break it up, you don't need to provide all the information in one go, unless you are concerned with network latency.
Show the company info with a company query. Add buttons for phones and adresses. Do forms for them
add a phones button to address as well.
If you want to put it all on one form (addresees and phones in list boxes. Break your queries up to polulate the list boxes on change of companyid.

This discussion is closed to new replies.