Correct Table Structure?

[DevastatioN]

Hello,

I am creating a database that is used to track lots of different types of documents. Such as letters, reports, books, etc.

However the actual tracking of the transactions of the documents is identical. Which scheme is best.

Scheme 1: Each type has it's own table with it's own unique fields, and all tables are linked the same way to the transaction table. The problem this causes is that referential integrity cannot be used.

Scheme 2: There is a main table that has only the item number, and type, which is linked to the transaction table one-to-many. However each type has it's own table, but is connected to the main table as a one-to-one relationship. This allows the main table to keep the transaction information, however breaks out into one-to-one tables based on the fields being different.

The main problem is, usually you would have an items table, and a transactions table, but because the fields are so different between reports, books, etc. If I put all the fields into the same table, and only display the correct ones on the form, all the reports will have null fields for everything it doesn't use, and it would be hard to manage overall.

If scheme two is used (which I think is the better one). How do you work with one-to-one tables in access, simple make a query for the form which takes the key from the main table, and the data from the other one-to-one table. When it's updated, the key is automatically filled into the one-to-one table?

Attached are two pictures of scheme 1 and scheme 2.

Thanks,

 

[MSAccessRookie]

Hello,

I am creating a database that is used to track lots of different types of documents. Such as letters, reports, books, etc.

However the actual tracking of the transactions of the documents is identical. Which scheme is best.

Scheme 1: Each type has it's own table with it's own unique fields, and all tables are linked the same way to the transaction table. The problem this causes is that referential integrity cannot be used.

Scheme 2: There is a main table that has only the item number, and type, which is linked to the transaction table one-to-many. However each type has it's own table, but is connected to the main table as a one-to-one relationship. This allows the main table to keep the transaction information, however breaks out into one-to-one tables based on the fields being different.

The main problem is, usually you would have an items table, and a transactions table, but because the fields are so different between reports, books, etc. If I put all the fields into the same table, and only display the correct ones on the form, all the reports will have null fields for everything it doesn't use, and it would be hard to manage overall.

If scheme two is used (which I think is the better one). How do you work with one-to-one tables in access, simple make a query for the form which takes the key from the main table, and the data from the other one-to-one table. When it's updated, the key is automatically filled into the one-to-one table?

Attached are two pictures of scheme 1 and scheme 2.

Thanks,

Since you have said that "the actual tracking of the transactions of the documents is identical", then you probably do not need separate tables, and I ask you to consider Scheme 3:

• There is a Main Table that has the Item Number, Transaction Number, and any other required information.
• There is a Transaction Table containing ALL of the transactions, that has a Type Code Field (FK From TransactionType Table) which will be for accessing and sorting the records.
• There is a TransactionType Table to list the Transaction Types that contains a Type Code Field (PK and FK for other Tables) whch will be used in Queries, Forms and Reports.

Although I do not have a screenshot (.jpg) to attach to show the relationships, I believe that this method could eliminate most of your problems.

 

[DevastatioN]

Yes, the tracking is the same, meaning I only need one tblTransactions, the separate table is for the document types themselves, which all have different fields.

I will look more closely at your scheme 3 later, thanks.

 

[HiTechCoach]

The main problem is, usually you would have an items table, and a transactions table, but because the fields are so different between reports, books, etc. If I put all the fields into the same table, and only display the correct ones on the form, all the reports will have null fields for everything it doesn't use, and it would be hard to manage overall.

IMHO, following the rules of data normalization, you would have one items table and one items attributes table. The attributes table would have on record for each attribute, not multiple fields per record. This avoids all the unused fields as you have described in your method.

You would also need an attributes lookup table that can be filtered but the item type. This way you data entry is limited to only the attributes that apply to this item type.

 

[DevastatioN]

So you are saying, there should be an attributes table linked as a one-to-many to the main items table (each item has multiple attributes).

The attribute table has fields such as "Attribute Name", and "Value"

so that... for example Attribute Name = "Book Title" and Value = "Access Manual" and then another with maybe Attribute Name = "Report Year" and Value = "2009" etc.?

So once someone selects the type of document on the main form, I guess I need to use code to insert all the things into the attribute field that go with each type. Example, when someone selects "Book" I need to automatically bring up the fields Book Title, Publish Date, Author, etc. by manually putting them into the attributes field.

Does this sound like what you're saying?

To MSAccessRookie:

Unfortunately the first line of your design is what is causing the issue:

There is a Main Table that has the Item Number, Transaction Number, and any other required information.

"other required information" is very different depending on the type of item. This is where I'm trying to eliminate blank null fields and organize the information so that each type has it's own table, or is clearly sortable.

 

[DevastatioN]

Actually, I'm gonna slightly reword a question here, because as much as I like the attribute idea (if I had it correctly), it would be difficult to manage in many ways for the users I believe.

My new question is, if I use scheme one, what problems will the database run into (I can see that I won't be able to enforce referential integrity in this system). However, each type has it's own table (and will have it's own form as shown), and each form will be able to have a subform of the transactions table.

So I can create Form: tblBooks Subform: tblTransactions
Form: tblReports Subform: tblTransactions
Form: tblLetters Subform: tblTransactions

And also be able to run all the queries correctly. But where will this system break down, what will I not be able to do?

I guess if this method really doesn't work, these are pretty different systems, I could just make tblBooks, tblBooksTransactions, tblReports, tblReportsTransactions, etc. Since they are truly different systems. However I wanted to mash all the transactions tables together since they will have the same fields. But there is truly no overlap in the systems, reports and books and etc. are all unique and don't overlap. Any overlap within the transactions table I believe could be done with a UNION query. I guess this is the most sane method.

 

[Pat Hartman]

With Schema1 there is no way to know which table a transaction is associated with.

Schema1 is wrong. Schema2 is correct. RI is Important. Don't leave home without it

 

[DevastatioN]

Thanks for letting me know that Scheme2 is in fact the correct one.

However which is the best way to work with so many one-to-one relationships like this, if I have only an entry in tblMainItem and one of the other tables, let's say tblBooks, the database will say you need a relating record in tblReports.

So I guess I have to add the ID in each table, even if some of the tables only contain null values. So in essense, I still get my blank data as I would in a single table, except that it's more organized. Is this correct?

 

[gemma-the-husky]

i think you need to consider what you want out of the system, when you analyse it - but you need to realy understand what a normalised structure IS, and what it isnt.

so start with a basic documents table.

so in here you have fields for document_name/document_date/last_revision_ref/author etc etc
(recommendation - dont put spaces in field names - use dashes or underscores - or capitalisation)

in this table you also have a field identifying which sort of document this is - eg letter/report/cv etc
so yuo probably need another lookup table for these, so you can add new document types in the future.

now the exact requirements of your system, may mean you need some other associated tables - perhaps an author table, to provide more information about each author.

it really depends on where you are going, but on the face of it, you should NOT need a proliferation of tables

 

[DevastatioN]

I understand what a normalized database is, and your system makes perfect sense, if all documents needed the same types of fields. Unfortunately where this breaks down, is that because the documents are so widespread, and different types of information are kept from many different types of documents, that is the problem. If they all need a Title, then that is one field, and that's fine. But when you dig deep and certain types of documents need 10 fields that all other documents will never use, and that many document fields don't overlap into anything common, this is where my problem lies.

In essense the data should be kept separate, but because everything needs to be tracked in the exact same way, the types that are usually never put together, are forced to being thrown together somehow.

 

[Rabbie]

I understand what a normalized database is, and your system makes perfect sense, if all documents needed the same types of fields. Unfortunately where this breaks down, is that because the documents are so widespread, and different types of information are kept from many different types of documents, that is the problem. If they all need a Title, then that is one field, and that's fine. But when you dig deep and certain types of documents need 10 fields that all other documents will never use, and that many document fields don't overlap into anything common, this is where my problem lies.

In essense the data should be kept separate, but because everything needs to be tracked in the exact same way, the types that are usually never put together, are forced to being thrown together somehow.

Just remember that just because a Parent record can have a child record doesn't mean it has to. You will need to make sure that the relational link models this. From memory the linktype will be 2 (A left Join) rather than the more usual 1 (An inner join) Then you can put the extra fields in a subtable linked to the document record.

 

[DevastatioN]

Ok, I have tried editing the one-to-one relationships so that they change the join type to 2 (I also tried 3). I then created a query with tblBooks and tblMainItems, but it still says "You need a relating record in tblLetters".

I am using my Scheme2, I can upload another screenshot of the changed join types if you wish.

I guess the question now is, can a one-to-one relationship define either table as a parent, and the other as a child. I believe in the end I will be using a single table, but I feel I need to look into the option of scheme2 depending on just how many null fields will included after the analysis is finished. Unfortunately because one-to-one relationships are so rare, I have no experience with working with them.

 

[Pat Hartman]

The relationships between tblMainItem and the three item types is 1-1 but it is sparse and that is fine. For each value of the primary key in tblMainItems only ONE of the three tables will have a matching value. tblMainItem needs an autonumber as its PK and the three related tables use long integers. In addition to the Type field, you need to move all the common fields to tblMainItem to make this structure useful. Only the unique items go into the three child tables.

 

[The_Doc_Man]

From a different viewpoint, I'll add that it is necessary for you to SCRUPULOUSLY abstract the business process to know what things must be in transactions and what things can be in one of the individual item tables.

Now, as to how to manage this, your problem will be in data entry of the class-unique fields. A parent form can handle the top-level transaction stuff. Here are a couple of off-the-wall ideas.

Case 1: Have a button that says "DETAILS" on the parent form. Make the OnClick routine look at the document type and have a SELECT / CASE construct to open up a class-specific sub-form bound to the correct sub-details table, in which you supply the linking number to the sub-form's filter property. Make this a pop-up form.

Case 2: Create a details sub-form on the parent form. Create the details sub-form to the same size for each document type. Leave the sub-form unbound until a document is selected. Then instantiate the form using the same kind of SELECT / CASE. Pass the current transaction number as the parent link and always use the same field name for the foreign key in the child table.

In either case, the maintenance is the same.

Step 1 - for a new type of document, add another sub-form for the details.

Step 2 - in the main form, add another case to the sub-form selector.

 

[DevastatioN]

Thanks for the ideas of how to hash out this system.

The Doc Man: Those are some great workarounds to those issues, thanks very much. I like those ideas very much

 

[DevastatioN]

The relationships between tblMainItem and the three item types is 1-1 but it is sparse and that is fine. For each value of the primary key in tblMainItems only ONE of the three tables will have a matching value. tblMainItem needs an autonumber as its PK and the three related tables use long integers. In addition to the Type field, you need to move all the common fields to tblMainItem to make this structure useful. Only the unique items go into the three child tables.

This is what I have been trying to do, can you give a bit more detail on how access allows this with referential integrity however, or if I would have to turn referential integrity off for these links.

As I said, it will not let me enter the information in only one of the three child tables, it will give me an integrity error unless each child table has a record that links to the main one.

For example, if my main table has the ID 5... if anyone of the child fields does not have a 5 in the one-to-one relationship linked field, it will give an error. This happens regardless of which of the JOIN Types I use, if referential integirty is turned on.

 

[Pat Hartman]

Your join is backwards. The main table needs to be the "parent" in the 1-1 relationship. It is also the table that has an autonumber primary key. Delete the join lines and draw them in the other direction. With a 1-m, Access knows how to build the relationship due to the relationship of the PK and FK but in the case of 1-1, from Access' perspective, either table could be the 1-side of the relationship so you need to be specific when you create it.

 

[DevastatioN]

Edited: Sorry, misunderstood your original post.

I got ti working, it wasn't a problem with the join types at all, but the actual direction the link was created. Thanks very much for all your assistance in helping me understant one-to-one relationships.