Noob design question

[Dchall_San_Anto]

This is a db design question. I am starting over with the database I have been using for a couple years. All I've ever done with db's is use them and make minor modifications, so my questions are usually pretty basic.

I got the file from a co-worker who lives in another town. He is totally untrained in databases and built it as a flat file. The db works well for what we do; however, I think I can speed up the data entry considerably by rebuilding it as a relational database.

The database is used to collect data about large acreage real estate transactions in Texas. On one project I will trace ownership of anywhere from one to fifty or more tracts of land among anywhere from five to well over one hundred different owners. Many times the buyers and sellers are the same people. Once we enter all the data, we use MS Word to merge the data into a report in the format used by our clients.

We collect information on buyers, sellers, type of instrument (deed, lease, etc.), where the instrument was recorded (county clerk deeds, oil and gas records, etc.), date of instrument, date of filing with the county clerk, volume and page in record book, legal description (which can change from transaction to transaction), and comments about specific points of interest to the clients.

I was going to build individual tables with autonumber keys for the names (of both buyers and sellers), addresses associated with the names (they move around), instrument types, location of filing, and legal descriptions. Then, to tie it all together, I was going to build tblInstrument based on the actual instrument. tblInstrument would have the volume/page, dates, and the specific details of each transaction. This table also needs to relate to all the other tables.

Here's my question: when I build tblInstrument, should I have a field for each key number of the various other tables or should I do it the other way around (each of the other tables holds the key field for tblInstrument)? It seems like I could do it either way, but probably one way is better.

 

[stopher]

Here's my question: when I build tblInstrument, should I have a field for each key number of the various other tables or should I do it the other way around (each of the other tables holds the key field for tblInstrument)? It seems like I could do it either way, but probably one way is better.

First let me say I know nothing about real estate

A question like this normally hinges on whether the relationship between tables is many-to-many, one-to-many or one-to-one. So you have to consider each relationship in turn.

For instrument and owner, this is many-to-many (an owner can have many "Instruments", an "instrument" can have many owners). So you probably want a InstrumentOwnership table as well as Instrument and Owner

For filing location then I would expect the FilingLocationID to appear as a FK in the Instrument i.e. This instrument is filed here. This is a one-to-many relationship (One filingLocation->many Instruments).

Legal desciptions (?). Do you have lots of them for one instrument. Again this is one-to-many but this time its one Instrument=>many Legal Desc (compare this with the above one-to-many). So I would have an InstrumentID as a FK in the LegalDesc table.

If you find instances where you think you can do it both ways round then ask youself if you have a one-to-one relationship. In which case you can either combine all the info into one table or put the FK in either table.

I've made some assumptions about your data so please be carefully about taking me too literally. The point is to understand the type of relationship which will give you the clue as to how to design it.

hth
stopher

 

[dsigner]

A relational database will be much better than the flat file - eventually. It is clear that it is quite complex so by the time you have finished you will know quite a lot about databases. I suggest taking things slowly. Keep your flat file and separate out one chunk of data at a time. Start with names and addresses. Put these in a separate file and make sure you can combine them back for the reports you want. Also that you are happy about adding editng deleting info in them. Once you have that working with the rest of ythe info still in a flat file you will have an idea of how relational databases work. So then you can take on another chunk. Contiune the process until you have a normalised database. That way you can deliver improvements to your business while you are learning the ropes. Normalisation does not have to be an all or nothing process