473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Relationship question

I have a couple tables in a database. One for vendors and includes all the contact information you would expect. I also tables for customers and employees. A lot of the information seems to be the same for all three tables such as address and contact information. However if I try to use relationship to connect each of the three tables to a fourth table containing all the contact info I am unable to create a customer without creating a vendor linked to the same address. I have tried one-to-many relationships from the vendor or customer to the contact info table. I have also tried one-to-one.

Is there another type of relationship I can try or set it up in another way? I have thought about denormalizing the tables or just going without relationships altogether and just looking up the info using and id or something.


Any thoughts?
Feb 28 '07 #1
2 1076
MMcCarthy
14,534 Expert Mod 8TB
Create the tables as follows:

tblAddress
AddressID (Primary Key - Autonumber)
AddressLine1
AddressLine2
AddressLine3
PostCode
Country
ContactName
Telephone

tblVendor
VendorID (Primary Key)
VendorName
AddressID (Foreign key reference to Primary key of tblAddress)

tblEmployee
EmployeeID (Primary Key)
EmployeeName
AddressID (Foreign key reference to Primary key of tblAddress)

tblCustomer
CustomerID (Primary Key)
CustomerName
AddressID (Foreign key reference to Primary key of tblAddress)

tblAddress has a one to many relationship with each of the other tables.

Mary
Feb 28 '07 #2
NeoPa
32,556 Expert Mod 16PB
Your tblAddress should probably look something like this :
Expand|Select|Wrap|Line Numbers
  1. tblAddress
  2. AddressID (Primary Key - Autonumber)
  3. VendorID (Foreign Key)
  4. EmployeeID (Foreign Key)
  5. CustomerID (Foreign Key)
  6. AddressLine1
  7. AddressLine2
  8. AddressLine3
  9. PostCode
  10. Country
  11. ContactName
  12. Telephone
Each of the FK fields would have to be defined to allow Nulls and one, and only one, of the three should always be filled.
Mar 3 '07 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: H Cohen | last post by:
Hi, I have a corporate database with about 60 different tables that spans manufacturing, accounting, marketing, etc. It is possible, but unwieldy, to establish a relationship for each table...
2
by: Stewart Johnson | last post by:
Hey All - I'm stuck while trying to write a schema, hoping someone can provide me with a flash of inspiration. One of the elements I'm writing has two attributes, both of which are optional....
8
by: Andante.in.Blue | last post by:
Hello, I have just inherited a legacy Access 97 database. While going through it, I noticed something strange... its Relationships window (the one accessed by Tools --> Relationships) is almost...
2
by: William Bradley | last post by:
Some help with relationships would be appreciated. I have read up on them but am still a bit vague ... The tables are as follows: 1. Main table -- this holds information about a product we...
2
by: Larry R Harrison Jr | last post by:
I have an Access 97 database with 2 tables that have a one-many relationship. I have a SQL statement in the "one" table which I want to execute and insert 7 records into the "many" table, and I...
2
by: Marco Simone | last post by:
Hi, I have 4 tables, tblCompany, tblA, tblB and tblC. tblA, tblB and tblC contain same type of data, they should be in one table, but since there are many fields, I split it into 3 tables. Each...
2
by: Phil | last post by:
I am learning Access, and have one question on relationships. How does one handle establishing a relationship that could be one-to-many, going either way between two tables? Example: One table...
3
by: Mason | last post by:
I have a table consisting of people and their attributes. I would like to create a relationship matrix table that records the relationships between the people. So the two tables would look...
1
by: Michael D. Reed | last post by:
I have two tables in an Access database with a Many-to-Many relationship, there is a connection table. The tables are , , and the connection table is this is a standard Many-to-Many relationship...
2
by: cspowart | last post by:
Consider first, table "A" : Plate Make Model ===================== ABC123 Ford F150 XYZ789 Dodge 1500 IJK444 Chev Silverado Then consider, table "B";
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.