Help with table structure for tracking Publications 1

[Boham]

I am working on a "Publications" database inwhich I have a table called "Pub_Index". This table contains necessary details for every publication inwhich the company has. It gets a little tricky in that we also have binders (which have their own ID) inwhich the contents fo the binder are the individual Publications listed in the Pub_Index table. The Pub_Index table contains (Pub_No, Description) and other details pertaining to the individual publication. Binder_Index contains (Binder_No, Description, Qty). Binder_Details contains (Binder_No, Qty, Pub_No) to track the individual publications that are contained in the Binder. I currently have a 1-many relationship for Pub_Index to Binder_Index and a 1-many relationship for Binder_Index to Binder Details.

Can you please tell me if this is a logical structure or if there may be a better way to handle this situation?

 

[ajetrumpet]

Boham,

I'd say this is a logical structure, sure, but that depends on what you are going to need to do with this data. Do you have something specific you want to do with it? What are your primary keys?

-J

 

[Boham]

One result is: I have a form/sub form set up to process orders. The Subform has 3 fields Qty, DocumentNumber, Description. The user enters the Qty and DocumentNumber, the Description is filled in for them. The DocumentNumber field comprises a dropdown that needs to contain "Pub_No" from the Pub_Index & "Binder_No" from Binder_Index. This would allow us to place orders for the individual publication as well as the Binders which contain all of the appropriate Publications

 

[BobRodes]

You're simply describing an associative entity (or "broker between two kinds of stuff") that is resolving two kernel entities (or "stuff") in many to many relationship, and yes, that is the logical structure of choice. You have numerous publications, and numerous binders full of publications. Each publication can be in any number of binders, each binder can have any number of publications. That's a many to many relationship, and the way to resolve it is to form another entity that is in one to many relationship with each. That would be your binderdetails table, although it could just as well be called publicationdetails if you think about it.

Whether there is a better way to do it or not I will not debate, but I will say that this is precisely the structure that relational database theory would offer as a solution. Now, what more precisely depends on what you are going to do with your database is the fields that you keep in each table, rather than the relationship structure itself.

I would change one thing in your structure, though. In your associative table, either add a primary key, or make Binder_No and Pub_No a compound primary key (this I would prefer personally). Make them the first fields in your table.

Bob

 

[Boham]

With this information Bob, I have now built the table structure and have it working properly in my forms...

 

[BobRodes]

Good job.