Relationships and table usage

[gwboolean]

I am in the process of setting up a database for item/document control, as well as other applications related to medical device/Pharma usage. Currently, it consists of 4 related tables (see attachment).
The first table (setMBIDNum) is a smart number generator that creates a 9 character string, with the first 5 characters being the smart part and the last 4 characters being an ascension number. These numbers must be unique and are applied to any item or document that is created. The second table, tblMBItemMaster, is a table that contains each item (part, product, utility, instrument, process, equipment, supply, ect.), basically anything physical. The next table, tblMBDocMaster contains any/all documents (policies, SOPs, Work instructions, shop floor papers, product briefs and so forth). The last table, tblMBItemRequest is a link table between tblMBMBItemMaster and tblMBDocMaster and is nothing more than a change request table. All items/documents created or modified are carried out through a change request created in tblMBItemRequest. Although tblMBItemMaster and tblMBDocMaster are identical in structure and certainly they could be combined I want to have specific things isolated into their own table.

Most, but by no means all, of the documents have a direct association with an item. Of course there are other tables that will be part of this e.g. a validation table, a development table, a QA table, et. al. Like the tables shown in the attachment, these will be related in exactly the same manner as tblMBDocMaster and will receive a smart number and be managed through the link tblMBItemRequest.

My question is this. Is this a good approach or should I be considering a different approach to handling the data and the relationships for this database? Also, is the fact that there might be documents that are not associated with an item a possible problem for me (an example would be a policy)?

I will be happy to provide additional information if what I have included here is not clear.

[June7]

So the numbers assigned to strItemID and strDocNum all come from setMBIDNum?

Using two tables when one can serve is usually a bad idea. Another field could be used to classify the records.

[ssanfu]

I would avoid the use of Text type primary keys.
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


I use an Autonumbertype field for the PK field in all of my tables. (so much easier IMO)
You can still have another field for the smart number generator that creates a 9 character string AND have it unique by setting an index on that field.

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers



My $0.02......

[gwboolean]

All numbers are assigned from setMBIDNum. This table is a smart number generator. I do understand that the two tables could be combined. However, Items and documents serve different purposes and are quite different things. While I can see that one table could actually serve all, I think that since different actions take place with say, items and documents, that it is better if they are contained in their own tables. Additionally, there will be other tables added for validation projects, development projects, QA and other activities. And the Item table is also meant to work with a completely different set of related tables, that will come later, than the document table.

[gwboolean]

Ssanfu, I do understand about using autoincrement number keys with tables. And I do have those in place and use them as the primary key where I believe it to be appropriate (tblMBItemRequest) is an example of that. However, tblMBItemMaster and tblMBDocMaster both are driven by their assigned, unique, smart ID number and I felt that it would be more appropriate that those should be the primary key, rather than the autoincrement number (which by the way both tables contain). I could be wrong about that, but it seems that the primary key should be based on what the driver is and what is used to define the table.

[June7]

Not seeing autonumber field in tblMBDocMaster.

I also have several dbs where I preferred to use a custom unique identifier text field as PK/FK. I don't even create autonumber field. One db has to import data from satellite field offices dbs (no network/internet access), autonumer PK/FK was totally impractical. Other dbs I found autonumber more useful. I have a db that had to merge data from several dbs, all of which use autonumber PK/FK. Building the code to accomplish that was a challenge.

[gwboolean]

June7,

So then you believe my approach to be OK? There were several coding challenges for my smart number table as well. I use Visual Basic for my applications and to manage my database. I am in such a habit of putting an autonumber field into a table that I do not even think about it even though I have no use for it in the 2 tables. One problem I am having with my VB application is that if I enforce referential integrity with the link table my program throws an exception stating the following:

~

Am I misunderstanding the usage of enforcing referential integrity?

[June7]

The 'smartnumber' table isn't even necessary. Code could locate the last incremented value from the two tables and build the next number from that.

I seldom set 'enforce referential integrity', often don't even bother with setting relationships - I manage data integrity with db design and code.

What is the code throwing that error trying to accomplish? If it is trying to create a record in a dependent table without first creating parent record, that would cause an error.

[gwboolean]

That is true June7. However, the incremental number is not, nor can it be a smart number. The smart number, as I am using it in this application, works like this. It consists of 9 characters. The left most 5 characters are the smart number, while the rightmost 4 characters are an ascension number. Each of the characters of the smart part of the number have meaning. For instance, the first character represent the owner of the item (generally a department). The second 2 characters represent the identity of the item (What it is in general terms, while the next two characters represent where the item is used. While not absolutely necessary for an application like this, most companies like to have smart numbers and like to use them. This is a fairly common usage in the medical device and pharma industries.
I was thinking about the possibility of the dependent record being created prior to the parent record, but my code is setup such that the parent record in the change request table is updated (saved) prior to the creation of the dependent record in the....... wait a minute. I need to go back and look, because I think I might have set the relationships backwards, with the item table being the parent and the change request table being the dependent. I think you nailed that one June7.

[June7]

As shown in the posted relationship diagram, tblMBItemRequest is dependent (child) table to both tblMBItemMaster and tblMBDocMaster. If you set referential integrity, it cannot have a record without records in both tblMBItemMaster and tblMBDocMaster first and strItemNum and strDocNum fields would both need data.

Should a record in tblMBItemRequest have data in both strItemNum and strDocNum fields?

Note: strDocRev appears to have a space showing in the diagram.

[gwboolean]

By the way, I dumped the referential integrity. Yes, that is correct, which I had already figured out from your previous statement. The child table for both parent tables (tblMBDocMaster and tblMBItemMaster) is the link table lnkMBItemRequest. When I went back and looked my code had the update occurring to the child table prior to updating the parent tables. Furthermore, while most documents are related to an item, that is not always the case, which by your explanation would also cause my code to throw an error. While it would not be difficult to go back and set the code to update the parent table(s) prior to updating the child table I see no reason to bother, since I really can't see any gained benefit from using referential integrity. Additionally, based on what you said, both of the parent tables would have to be updated and tblMBDocMaster is not associated every single time with tblMBItemMaster. However, even that could be done, if I am willing to expand the meaning of items to include such things as the company, a business unit or even a department, et. al.