UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> New User    
 
   
tina t
post Feb 16 2020, 10:32 PM
Post#21



Posts: 6,459
Joined: 11-November 10
From: SoCal, USA


QUOTE
creating a 1 to 1 relationship for every relationship that is required takes the number of records from dozens in some tables to conceivably hundreds

well, the number of records in a table is largely irrelevant; a well-designed relational structure with properly indexed tables and well-written queries can handle millions of records - if not in an Access backend db, then certainly in SQL Server, Oracle, etc.

at this point, the issue is your relationships. the table relationships in a database should mirror the "real-world" relationships of the entities, and in the real world, one-to-one relationships are pretty rare. ditto in relational dbs; you only use one when you must have one record in a table related to ONLY one record in another table. i find it very hard to imagine that you need any one-to-one relationships, let alone multiple such.

keep in mind that a one-to-many relationship supports multiple records in a child table that are related to a single record in a parent table - but multiple child records are not required. if i may suggest, go back to the book, and other sources that you've used to study relational design, and read through them again. i'm not being a smart-aleck. it can be hard to get a clear understanding of relational design principles. it took me a few years, and lots of practice, to get comfortable with the concepts and how to apply them successfully to real-world situations. and i still struggle with some of the more convoluted processes at times.

QUOTE
you break it up to certain extent ... maybe by sub dividing some of the larger tables based on county

no, no. as i said before, the number of records in a table is irrelevant. don't start breaking normalization rules by putting data (such as county names) in table names, or in field names. keep to the straight and narrow; store data in fields.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
MadPiet
post Feb 16 2020, 11:13 PM
Post#22



Posts: 3,579
Joined: 27-February 09



Amen to the normalization advice. I think that most people have a hard time with normalization until they really understand it well. And it takes a lot of practice. I certainly had a hard time with if for a long time.
Go to the top of the page
 
patton610
post Feb 17 2020, 01:25 AM
Post#23



Posts: 4
Joined: 5-January 20



purpose has changed as availability of data and an appreciation of the capabilities of access has increased. oil companies can make mistakes and create inaccurate accountings of what they think mineral owners own. If a division order is signed with the incorrect information, at best the mineral owner is underpaid and at worst they amount to fraudulently claiming ownership of mineral interests. DB was created to facilitate the checking of customer records against oil company ones to insure accuracy.

the database originated as a inventory of oil royalty division orders which are executed on a well by well basis often with many wells on the same lease. the original table was the result of recording data from physical division orders and limited supplementary data necessary to execute the orders, as well as keep track of the wells as they began producing.
this resulted in the division order table which had the following fields

this represents the table as it is now but several of these fields will likely be deleted as the data is moved to more specialized tables similar to the company table

ID- this is the primary key and the number written on the envelope containing the physical copy of the record
oil company- the company marketing the oil or gas or both ***** will become a foreign key and will be the primary key in the company table
company specific ID number- a number that the oil company assigns to royalty owners for their own internal record keeping
state company number- a unique number similar to api that a state assigns each company for record purposes
operator - a company, sometimes a contractor, but often a organization within the oil company itself that oversees day to day operations of a well.
state operator number-
lease name- this is the often oil company specific name for the lease in which the well is located
state lease number- unique lease number assigned by the state
well- designation of the well within the lease
well api- a number assigned by the state that is a unique identifier, can be used to track production through well etc
legal description- a somewhat idealized description of the real estate a well pulls oil from
DO date- when it was prepared by the company
DO effective date-the date from which the terms of the DO are in force.
lease royalty rate- a value derived from the lease (legal document) signed between the oil company (lessee) and the royalty owner (lessor) this type of lease is the subject of the lease table
decimal interest per division order- a value derived from the royalty rate and the percentage of ownership the value that the inventory was created to check
notes- place to list issues with individual orders discrepancies etc

Company Table

Name - **** primary key and foreign key in the DO Table
Address- street address or po box
city
state
zip
phone
state company number
company specific ID number - same as in DO table
email
notes - misc such as secondary addresses


Future tables

Leases- this table will likewise be based on physical documents and will represent a leasing agreement between the royalty owner and an oil company leases are sometimes decades oil and the terms can vary. Usually they are doe on a section by section basis and wells can cross sections so more than one lease can be at play for a single well/ division order. additionally multiple leases can exist for the same section of land because leases cover bot just acreage but certain depths. Usually there is a period of time in which the oil companies have to drill otherwise the lease is terminated... so its entirely possible to have a lease on the books and without any division orders at all

property inventory - a listing of all royalty interests held by customer/user ideally would be linked to lease table but not directly to DO table fields similar to company table in the sense that it hierarchical location information , would be used primarily to keep track of what property has been leased and what has not.... keeping in mind that the same property can have leased and unleased minerals


so a single well/ DO can be subject to multiple leases and individual sections of land in the property inventory can be subject to multiple leases often with different companies.

pulling all of this together was going to be the job of the reports whenever I got around to writing them. there are professionals that just manage minerals, but the expanded goal is to create a mineral management database for the customer that will limit the time investment needed to manage them by automating retrieval of data which will hopefully eventually include links to pdfs of the original documents so... from checking numbers to managing everything. I think that's called mission creep. that may have been too long sorry ... thats about as detailed as i can get without dragging everyone into oil and gas law. I hope its clear. I went back and looked at the Hernandez book again and his suggested method results in a lot of redundant data which relates back to the original issue that brought me here (the use of a combo box to fill in multiple fields) which I wanted to use to speed up data entry... but anyway... thank you all for the course corrections
Go to the top of the page
 
MadPiet
post Feb 17 2020, 01:41 AM
Post#24



Posts: 3,579
Joined: 27-February 09



Okay, so this is a gnarly question.

This is how my professor explained how to start this process. Write down a brief/succinct description of what you're modeling - it's basically nouns and (mostly action) verbs. For example...

"An entity can own mineral rights to one or more (Mines?), and an entity can be either a person or a corporation."
Each mineral right can be owned by several entities at once.

<Entity>---(1,M)---Owns---(M,1)---Mineral Right

(Sorry, skip the Individual/organization part for now... that's subclassing, and you're nowhere near there yet.)

Since "Owns" is in the middle of a Many-to-many, it becomes its own table. (Just the rules when you do a translation from database rules to actual tables... can't remember what it's called anymore.). You'd implement it like this ("Owns table's two foreign keys (entityID and MineralRightID) become the primary key of the join table... it's just the way the design works. So the tables look like this:

CODE
CREATE TABLE Entity (i
    EntityID INT IDENTITY PRIMARY KEY,
    EntityName VARCHAR(50) NOT NULL
);

CREATE TABLE MineralRight (
    MineralRightID INT IDENTITY PRIMARY KEY,
    MineralRghtName VARCHAR(100) NOT NULL
    …
);

CREATE TABLE Owns (
   EntityID INT NOT NULL,
   MineralRightID INT NOT NULL
CONSTRAINT pkOwns PRIMARY KEY (EntityID, MineralRightID),
FOREIGN KEY EntityID REFERENCES Entity(EntityID),
FOREIGN KEY MineralRightID REFERNCES MineralRight(MineralRightID)
);


Then you do that for every other object in your "world" that you need to keep information about. <g> Simple, right?
Go to the top of the page
 
patton610
post Feb 24 2020, 06:30 AM
Post#25



Posts: 4
Joined: 5-January 20



Im getting a better sense of it now maybe..... the api number is reference field in my DO table, and is not actually a part of the physical division order... I kept it there because from that one piece of info you can pull all sorts of info online, and its completely useless absent other identifying info... but each one is unique, which means its a candidate field for a foreign key in my DO table, and a primary key in the lease table where I am really stressing is that due to there being many wells on a particular piece of land there is going to be a lot of redundant information that will have to repeatedly replicated .... unless I create a separate table for the actual lease info with single entries for each lease and the only shared information between the intermediate table (which sounds like the owns table you mention) being numbers and such used for calculations in reports and the like. what I ended up doing in the case of the DO table for the oil companies is creating a combo box that uses the oil company name which is the primary key in the oil company table as the bank it pulls its selectable data from. I I dont know if this makes it a foreign key or not. I have entered a lot of data but am just starting to create relationships. seems like there would be some option similar to selecting the primary key, but I've yet to find it.
Go to the top of the page
 
tina t
post Feb 24 2020, 03:43 PM
Post#26



Posts: 6,459
Joined: 11-November 10
From: SoCal, USA


QUOTE
what I ended up doing in the case of the DO table for the oil companies is creating a combo box that uses the oil company name which is the primary key in the oil company table
...
I have entered a lot of data but am just starting to create relationships.

if you created a combobox in the table - it's called a Lookup field - that's a bad idea. it's going to cause a lot of headaches down the road if you don't change it to an "ordinary" field. if you referred to a combobox in a form - they're fine in forms, very useful - then you're getting ahead of yourself.

in fact, entering "a lot" of data before setting up table relationships is way backwards. define the table relationships; set up the tables and relationships in a database; enter some representative data as needed to test the relationships. once that's done and the basic structure appears sound, then write some test queries to check out data retrieval from multiple related tables. if you encounter issues, then go back and check query design and, if necessary, recheck tables/relationships design.

there is just no quick-and-easy way to do this. understanding and applying relational design principles is difficult for probably most people; we all had to do it, and it's just not easy.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
patton610
post Feb 24 2020, 09:54 PM
Post#27



Posts: 4
Joined: 5-January 20



Thanks yeah I ment to say form the actual field where its stored is just a text field. When all this started the first table and first foem were just going to be a neat way to view information in a more digestible manner. So lemme go and figure what the tables should eventually look like. The actual lease documents are contracts can be pages long with clauses and and such. So far as they relate to other tables in access, they need to be associated with a number of division orders on one side and usually one section of land on the other (which will come from a property inventory)... but certain values used in calculations show up there too.... its probably impractical to store the entire text of a lease, but limitations and percentage values can be pulled to check against division orders etc. The actual test can be linked to the records in a lease table. The truck is creating a standardized field list because some leases are decades old and some are brand new and are written to different standards
Go to the top of the page
 
GroverParkGeorge
post Feb 25 2020, 08:50 AM
Post#28


UA Admin
Posts: 37,000
Joined: 20-June 02
From: Newcastle, WA


Please do invest time learning how Relational Database Applications need to be designed. You may have learned something from entering all that data--to be precise, you learned how hard it is to work with a less than valid table design. Nothing like experience to drive home a point....

We have some excellent resources. Start here.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
patton610
post Feb 26 2020, 08:29 PM
Post#29



Posts: 4
Joined: 5-January 20



Oh I read hernandez's relational database design front to back, the issue I am am having is visualizing the relationships because my database is not built on a customer inventory, order model and so none of the examples in books are really relevant. The data I have entered has been from physical forms, the first table I made was data pulled from the same type of document across multiple companies, the second was the table for companies themselves which allowed me to move a bunch of company specific over and reduce the company data on the first table to a single field. where I am at now, is trying to figure out the best way to store information related to the the other two large subjects I'm dealing with leases, and deeds. The table that I have deals with division orders which are sort of the children of leases, which are other offspring of deeds. Eventually I want to be able to create reports that will pull all relevant data from the other two tables to answer queries about a record in any given one. I keep going back and forth on how best to create the other tables and what fields they should contain.

Converting non standardized legal documents into standardized fields is where I am stuck now. A single deed can convey many different sections or sections of sections in different blocks so its not as simple as just linking to the record for the legal doc as its own entity, (which is necessary) , but the to create a normalized tables, I need to break up what in many cases are multivalue fields on the document into different table that would be a bank for the original document with everything split up into sections and quartercalls as the smallest unit. I am tryig to look at the advantages of a single large bank for all deeds versus a different bank for every deed . A composite primary key would almost be necessary since sections are entered by block and survey on the deeds with no indexing number like an order number etc.

I may just create single record tables to play with the relationships before doing anything further with data. I was kind of worried about doing that, because i read that certain controls are limited to the data in place at the time of control creation for data used in combobox (selectable options when a combo box is used in a form)

at any rate im getting through the 2016 bible now from ... oh I forget its a large education/textbook company
Go to the top of the page
 
LendelS
post Feb 28 2020, 04:03 AM
Post#30


Banned spammer
Posts: 1
Joined: 28-February 20



I'm also a new user in this community. I would like to know more about this topic. Thank you!
Go to the top of the page
 
tina t
post Feb 28 2020, 03:10 PM
Post#31



Posts: 6,459
Joined: 11-November 10
From: SoCal, USA


@LendelS
there are several posts with links in this thread. suggest you start with those.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    7th April 2020 - 12:08 PM