OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Help with tables - relations - keys
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Tue Feb 02, 2010 8:04 am    Post subject: Help with tables - relations - keys Reply with quote

Table relations conceptual part is hard for me to grasp. I need some help with understanding what tables to create and relations, primary and secondary keys. Here's the DB scenario. Not sure if all the table are necessary? Hope I did not leave anything out. Just edited the keys again!

In Delivery Information Table > The Ticket Number is unique per-occurrence.

1 farm can have 1 to many fruit types.
1 farm can have 1 to many delivery tickets.
1 farm can have 1 to many agreement types

Tables:

>Farm Information
ID (primary key)
Farm Name (secondary key)
Contact Name First
Contact Name Last
Phone 1
Phone 2
Address
City
State
Zip
Memo

>Custom Picking
Farm Name (primary key)
Contract Start Date
Contract End Date
Insured
Insurance Code

>Contract Leasing
Farm Name (primary key)
Contract Start Date
Contract End Date
Insured
Insurance Code

>Fruit Type
Farm Name (primary key)
Fruit Type

>Delivery Information
ID (primary key)
Farm Name (secondary key)
Delivery Company Name
Contract Name
Contract Number
Ticket Number
Fruit Type
Fruit Grade Code
Fruit Weight

>Farm Harvest Information
Farm Name (primary key)
Estimated Acres
Estimated Weight
Memo
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 02, 2010 8:42 am    Post subject: Reply with quote

Quote:
Farm Name (primary key)

Realy sure there are no 2 equally named farms?

Always use some auto-ID of type integer. It can not be wrong, may be clumsy at worst.


Quote:
1 farm can have 1 to many fruit types.
1 farm can have 1 to many delivery tickets.
1 farm can have 1 to many agreement types


So you have three many-to-many relations which requires 7 tables.
a table of farms with auto-integer PK
a linking table with Farm_ID and Fruit_ID, building together one PK
a table of fruits with auto-integer PK

the same table of farms with auto-integer PK
a linking table with Farm_ID and Ticket_ID, building together one PK
a table of tickets with auto-integer PK

the same table of farms with auto-integer PK
a linking table with Farm_ID and Agreement_ID, building together one PK
a table of aggreement types with auto-integer PK

In table design you can select both integer columns of a linking table and mark them as one primary key.
Don't get mad when the relation designer does not accept all the relations in one go. Save, close and try again.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Tue Feb 02, 2010 9:10 am    Post subject: Reply with quote

Thanks Villeroy.. you got me, yes farms can have the same name..did not catch that.
The more I look at this, I think I might need another table? A farm name can have 1 to many contract names and contract numbers. Contract names and contract numbers are unique as related to each other. Though a Contract name can have 1 to many contract number. Hope this makes sense, this to me is one of the hardest parts of setting up a DB.

I am going to look back at the rest of your post, thank you.

> Farm Contract Information
ID (primary key)
Farm Name (secondary key)
Contract Name
Contract Number
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 02, 2010 9:26 am    Post subject: Reply with quote

You can use a contract number (automatic or self-made) AND enforce unique names.
This is what the index design does when you add additional indices independent from the PK (Tools>Indices... in table design view)
Notice that there is also a field type VARCHAR_IGNORECASE where "Farm" equals "farm".
The PK is the one you use in another table as "foreign key" when you refer to a distinct farm by means of a Farm_ID referencing a distinct row in the farm table's. A value in a "foreign key" points to a distinct row number in another table's primary key.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Tue Feb 02, 2010 10:52 am    Post subject: Reply with quote

Sorry for the long posts. I added a farm contract Information table. 1 farm can have 1 to many farm contract. The 4 relations are many to many? The field farm name that is repeated in several tables, do they need to be connected by foreign key to each other?

The tables custom picking (our records) and contract leasing (our records) are different then the contract number field (member delivery number, assigned by company buying product). Sound confusing with all the contract tables. Not sure I can combine the 2 tables “custom picking” and “contract leasing”. Both could occur with the same farm name, so left them apart.

Still working on this on paper.

Tables:

>Farm Information
Farm_ID (auto-integer PK) >linked to Fruit_ID (table-fruit type) & >linked to Ticket_ID (table-Delivery Ticket Information) &>linked to Agreement_ID (in both-custom picking & contract leasing tables) &>link to Contract_ID (table-Farm Contract Information)
Farm Name
Contact Name First
Contact Name Last
Phone 1
Phone 2
Address
City
State
Zip
Memo

>Custom Picking
Agreement_ID (auto-integer PK)
Farm Name
Contract Start Date
Contract End Date
Insured
Insurance Code

>Contract Leasing
Agreement_ID (auto-integer PK)
Farm Name
Contract Start Date
Contract End Date
Insured
Insurance Code

>Fruit Type
Fruit_ID (auto-integer)
Farm Name
Fruit Type

>Delivery Ticket Information
Ticket_ID (auto-integer)
Farm Name
Delivery Company Name
Ticket Number
Fruit Type
Fruit Grade Code
Fruit Weight

>Farm Contract Information
Contract_ID (auto-integer)
Farm Name
Contract Name
Contract Number

>Farm Harvest Information
ID (auto-integer)
Farm Name
Estimated Acres
Estimated Weight
Memo

1 farm can have 1 to many fruit types.
1 farm can have 1 to many delivery tickets.
1 farm can have 1 to many agreement types
1 farm can have 1 to many farm contract types
_________________
Vista 64-bit, Ooo3.2 Java 6, ud 17
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 02, 2010 11:22 am    Post subject: Reply with quote

I call it many-to-many when each thing belongs to many items and simultaniously each item belongs to many things.
I call it one-to-many when each thing belongs to one item OR each item belongs to one thing.
A one-to-one relation is usually the relation between the fields of one table where each attribute belongs to the attributes in the same row, but there are use cases where you want to associate one thing with a whole set of one item's attributes or none.

Do not copy names into the other tables. Always use the primary keys in the other table.

Many-to-many works through a third table. If each farm has more than one product you need a glue table of with 2 fields ProductID and FarmID. Each row describes a combination of one procuct with one farm.
http://user.services.openoffice.org/en/forum/download/file.php?id=2879 [movies belonging to genres]
http://user.services.openoffice.org/en/forum/download/file.php?id=1715 [one-to-one relation]
http://user.services.openoffice.org/en/forum/download/file.php?id=6362 [meeting of clients with emplyees]
http://user.services.openoffice.org/en/forum/download/file.php?id=6481 [invoices with products (only the spreadsheet form works when you register the DB as "Invoices")]

As far as I remember, all those example use the same type of forms to edit the ID numbers of many-to-many relations by means of list boxes. The user edits numbers without ever seeing them.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Tue Feb 02, 2010 11:42 am    Post subject: Reply with quote

Thanks, very helpful.. I copied and pasted my old 1 to many relations, should read many to many. thanks for the help understanding relations.

When you said do not copy names into the other tables, do you mean the field(s) - farm names? Just use the primary keys in those tables? Remove farm names from all the tables, only leave farm name in the farm information table.

Villeroy wrote:
I call it many-to-many when each thing belongs to many items and simultaniously each item belongs to many things.
I call it one-to-many when each thing belongs to one item OR each item belongs to one thing.
A one-to-one relation is usually the relation between the fields of one table where each attribute belongs to the attributes in the same row, but there are use cases where you want to associate one thing with a whole set of one item's attributes or none.

Do not copy names into the other tables. Always use the primary keys in the other table.

Many-to-many works through a third table. If each farm has more than one product you need a glue table of with 2 fields ProductID and FarmID. Each row describes a combination of one procuct with one farm.

_________________
Vista 64-bit, Ooo3.2 Java 6, ud 17
Back to top
View user's profile Send private message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Tue Feb 02, 2010 12:09 pm    Post subject: Reply with quote

I am a little confused here. The table fruit type is similar to the glue table you described. If I remove farm name from the fruit type table as I previously understood, that leaves a table with the fields fruit_ID and Fruit Type, w/o a farm name. Will this table work for the glue table?, or do I need a table that has fields - product_ID & farm_ID. I can change the wording of Fruit_ID to Product_ID of course.

How are the glue table relationships defined in relations to the other tables, many to many to Product_ID and Farm_ID?

Villeroy wrote:
.Many-to-many works through a third table. If each farm has more than one product you need a glue table of with 2 fields ProductID and FarmID. Each row describes a combination of one procuct with one farm.

_________________
Vista 64-bit, Ooo3.2 Java 6, ud 17
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Feb 02, 2010 12:32 pm    Post subject: Reply with quote

No, your fruits are not similar. Every fruit type is associated with exactly one farm name.
I mean this ...
Code:
tblFarms..........tblFarmsFruits........tblFruits
[ID] 1---------n [FarmID ; FruitID] n--1 [ID]

... and all of the linked databases show this pattern somewhere in the relation window.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Tue Feb 02, 2010 2:19 pm    Post subject: Reply with quote

cannot get the relatiohship to stay in the new glue table. When I close it, they are gone when I open it...
_________________
Vista 64-bit, Ooo3.2 Java 6, ud 17
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Feb 03, 2010 5:32 am    Post subject: Reply with quote

curly wrote:
cannot get the relatiohship to stay in the new glue table. When I close it, they are gone when I open it...

Both side of a relation have to share the same data type in order to do the magic.
A primary key on the 1-side, a foreign key on the n-side. The n-side must not have any values without equivalent on the 1-side.

If the Base-GUI allows you to draw the line between 2 fields but there does not appear the 1 and the n, then there is no primary key involved or it is one of those very disturbing hick-ups in Base. Sometimes I can drag one line, but not the second. Saving, closing and reopen the database works for me in this case (if the above prerequisites are fulfilled).
In general you have to finish the table design (fields, types PK and indices) before you add the relations and finally you add some test data.
To modify relations it is often required to delete the test data.
To modify the table design and indices you often need to remove the table's relations.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Wed Feb 03, 2010 6:26 am    Post subject: Reply with quote

the data type did not match in the tables Smile... Is there anyway to show the tables and relationships, in a text format to post on here w/o typing it all out? Not sure I have the join table correct.

[quote="Villeroy"]
curly wrote:
cannot get the relatiohship to stay in the new glue table. When I close it, they are gone when I open it...

Both side of a relation have to share the same data type in order to do the magic.
A primary key on the 1-side, a foreign key on the n-side. The n-side must not have any values without equivalent on the 1-side.
quote]
_________________
Vista 64-bit, Ooo3.2 Java 6, ud 17
Back to top
View user's profile Send private message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Wed Feb 03, 2010 7:15 am    Post subject: Reply with quote

Question regarding the join table
Tables
>Farm Farm_Product Products

Farm_ID 1........n Farm_ID
Product_ID n.........1 Product_ID

Do I need a join from Product_ID to Farm_ID?
_________________
Vista 64-bit, Ooo3.2 Java 6, ud 17
Back to top
View user's profile Send private message
curly
Power User
Power User


Joined: 12 Jan 2010
Posts: 81

PostPosted: Wed Feb 03, 2010 12:48 pm    Post subject: Reply with quote

Having difficulties with table relations and tables. Not sure how to add multiple - sub farms names, product, and dependent harvest information to sub farms. Forms wiz will not let me create a sub_form with sub farm names in it, based on the this type of relationship. How can you associate multiple products, sub farm name, and harvest info, related to sub farm names. Or can I add these farm attributes another way?

Is this correct?

1-1 = many to many?
n-1 = one to many?
n-n = one to one


Created,
Farms 1 ---n Farm_Products n ---1 Products
Farms 1 ---n Sub_Farms n ---1 Sub_Farm_Name
Farms 1 ---- n Harvest n -----1 Harvest_Info

All the other tables below are Farm 1 ----- 1

Contract_Info
Custom
Leasing
Ticket_Info
_________________
Vista 64-bit, Ooo3.2 Java 6, ud 17
Back to top
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Wed Feb 03, 2010 3:51 pm    Post subject: Reply with quote

1-1 means a one-to-one relationship (in many cases where a 1-1 relationship is used between two tables, the tables should instead be merged into one)
n-n (often written m-n) means a many-to-many relationship (which in relational databases is implemented as a linking table, the "glue table" that Villeroy describes).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group