How to create a one to one relationship in base

[overflowscott]

Forgive me. This should be a simple task but i am struggling to find the answer as I am very new to database design. Here is my challenge. I am building a contact management system database for my personal use.

In this case I have several different contact types:

• Lead
• Customer
• Colleague
• Supplier

In the sales cycle, Contacts are first identified as being a Lead, Colleague or Suppliers with the later two requiring no relationship with another other Contact Type. Now, while a Lead will always be a Lead, they may also become a Customer at come point on our process with very different attributes.

So I have created the two tables:

• Leads
• Customers

As I see it, a Lead will have either ZERO or ONE Customer related and Customers will always have exactly ONE Lead related. The ONE to MANY relationship I can create, no problem. But I am confused as to how I create the ONE to ONE relationship going back in the GUI. I am using LO 6.0.2.3 with the HSQLDB format. I know zero database languages and am reliant to the GUI. That being said, I am willing to learn.

Thanks in advance for responding.

Scott

[Ratslinger]

Hello,

One-to-One relationships are not very common. They require a unique field related to another unique field. As an example, you may have a table of countries. You also want to show a map for each country. As this map takes up considerable data, it is to be in a separate table. You only want one map per country. The primary table has:

ID      int              Key field
Name    varchar(50)      set field as 'Unique'  -    not part of key but not duplicated

The associated table contains the map:

ID      int              Key field
Name    varchar(50)      set field as 'Unique'  -    not part of key but not duplicated
Map     Blob

Now you can create a relation between the primary and map tables on the “Name” field of each and thus a one-to-one relationship.

Now in your situation not certain this is the way to go. Don’t know enough about your entire process to guess. If using this, it may be based upon the contact name. But what if you end up with two or more contacts with the same name? Now it’s a problem.

[overflowscott]

OK. This is very helpful. Thank you. What I think I can use is the customer ID that is provided by the company I work for. Just so that I understand, I would NOT make that the primary key in the Customer table, but a unique field that I would then link to the lead’s record in the Contacts table. Would I then create the Customer table without a Primary Key and just not link that key?

Thanks Again.
Scott

[Ratslinger]

First, every table must have a primary key to access data.

To make this as simple as possible, here are relationships:

one-to-one                 unique data to unique data
one-to-many              unique data to non-unique data
many-to-many           non-unique data to non-unique data

So if you are using this customer ID, let’s say it is the primary key in the Customer table. It is unique.

Now to create a link back to the Primary Table, that table must contain a column which has that same exact value and the column would need to have unique values for each record. Since not every record in the primary table will have a Customer record, this is not a solution.

Again not knowing all about what you have, it appears the consistent unique feature in the primary table is the Primary Key field.

[overflowscott]

OK. Thank you again. I think I am getting it. I appreciate your answers and your time. And yeah, I certainly understand what it means to not understand. Be well. Thanks,
Scott