Solved Table Structure (1 Viewer)

[Romio_1968]

I am trying to figure out the best structure for the following issue.
I have a database of a bookstore (a library)

for each book, i have to store the following data:
Title
Authors
Publisher
year
Domain
Inventory_No
All tghese fields may have duplicates (Same title for different books, same author/authors for many books, and so on)
Each book (every copy) wil have a inventory_No., so this field is unique. As example, if I fave 5 copies of the same book, each one wil have its own inventory_No

Each book may have a single author or multiple authors.

Issues:
When I am adding the title, i must be warned if the same title exists. If all the other fields except Inventory_No are the identical (it means that i already have that book), i should have the option to add more inventory numbers for this title (a single one, or as many no. as copies I add). If it is only a name coincidence, it slould let me to continue with the new record.

The book may have none, one or multiple authors (no upper limit). I should be able to choose from a list of existing or add new author names, as many as i need, for the same title.

I should be able to add all this data in one form. Moreover, if I have more than one copy of the book it should be able to alocate inventory_no for each copy,

Thank you for your help.

 

[XPS35]

I think you need at least these tables:
Book
Author
Book-Author
Copy (inventory no)
Domain
Publisher

If a book can have more than one publisher or domain, you need extra junction tables.

 

[Pat Hartman]

Welcome aboard @Romio_1968

If the book is published in the US, it will normally have an ISBN which should be unique. Or maybe even a Library of Congress number which would also be unique. Not sure about bar codes but those are probably unique also.

You would always use an autonumber as the PK just in case you didn't have one of the other unique identifiers. As part of your data entry validation, I would look for duplicates in the title and warn but not prevent storing a duplicate. A lot depends on your data discipline. the combination of title + published year is probably unique.

Normally, a book would not have multiple publishers, however, depending on the Author's contract, subsequent printings might be done by different publishers and once the copyright expires and book enters the public domain, who knows what the rules are.

 

[Romio_1968]

Pat Hartman multiple authors, not publishers​

 

[plog]

Put aside everything after you typed after "Issues:". Those have to deal with forms and are the last part of building a database. First you need to set up your tables and fields correctly.

XPS gave you a great start. I suggest you set up those tables and include whatever fields necessary in each, complete the Relationship Tool in Access. Then either screenshot it or upload that blank database so we can help you validate it.

 

[Pat Hartman]

Maybe you should read my remarks again. I didn't say there WOULD be multiple publishers. I mentioned that there COULD be. Since we have NO IDEA what your universe of books might be or the age of the books in your library, you could be in a situation where you have copies of the same book issued by different publishers. I also think that paperbacks and hard cover books sometimes use different names for the same publisher.

 

[GPGeorge]

Welcome aboard @Romio_1968

If the book is published in the US, it will normally have an ISBN which should be unique. Or maybe even a Library of Congress number which would also be unique. Not sure about bar codes but those are probably unique also.

You would always use an autonumber as the PK just in case you didn't have one of the other unique identifiers. As part of your data entry validation, I would look for duplicates in the title and warn but not prevent storing a duplicate. A lot depends on your data discipline. the combination of title + published year is probably unique.

Normally, a book would not have multiple publishers, however, depending on the Author's contract, subsequent printings might be done by different publishers and once the copyright expires and book enters the public domain, who knows what the rules are.

At the risk of throwing in only a slightly relevant link, you might want to consider the table design behind the PowerApps application demoed in this presentation.

The objective is similar, but not exactly the same. I intended to include other media, such as DVDs, as well as books. Hence, "Publications" instead of "Books". And, since DVDs have "creators" who are musicians, etc., and Books have "creators" who are authors, I choose that name. Otherwise, the parallels are there. One or more authors can work together, so you have a separate table for authors and a junction table to related one or more authors with one or more books. I think "genre" is a close match with your "domain", but it might depend on what you mean by that name.

Note that I do track the ISDN from books, as Pat described. If you want to track individual copies of a single title, i.e. if you have 12 copies of the same book in the same printing, etc., you'll need that Copies table. I would NOT put that into the Books/Publications table as it would create many duplicates of the same title, etc. The copies table has two fields, the foreign key identifying the Book, by its BookID and the InventoryNumber of each copy. Usually, such tables have their own Primary Key.

 

[Isaac]

+1 for anyone suggesting you first make database Relationships and Entities a study before doing anything.

If you think of every Entity you have, then think of the relationship between that entity and any other entity(ies), frankly that will accomplish the 99% of guidance to design tables.

If a book has authors, but can have >1 author, you probably need an Authors table (and so on).

I know that is going to be considered a major oversimplification by some, but it may help given where you're at.

Also, as early on as possible you should believe the general axiom which is: When the Business managers assure you a certain thing will never change, don't depend on that being accurate.
This is ESPECIALLY important when choosing Primary Keys and Indexes, IF you decide to use Natural keys, which I hope you don't.

Anyway, imagine the concept of Gender.
Now think of what that concept was 20 years ago.
Now think of designing tables for a Medical business that stored patient demographic information.
Assume Gender has usually been stored as a one-to-one relationship with Patient.

Now think of the present. Who would have guessed 20 years ago that it may be useful some day to have one Patient related to multiple Genders over time? But I'm guessing 10 years from now, that will not be unheard of.

Suddenly, the simple drop-down box on webpages becomes obsolete.

Never say never. When designing your tables imagine 'change' scenarios like this and ask yourself how difficult would it be to adapt to that change? When the change scenario becomes "reasonably possible" and the difficulty is also "substantial", reconsider.

Might not matter to your early projects, but the discipline of thinking that way will be valuable very often in your dev life...

 

[MajP]

You are working with Specific/
So a you need a generic book has authors, publisher, illustrator, publicationdate, etc.

If you have 20 copies of that book then you do not want to add that information more than once
You then need a Book Details table for each instance of that book

tblBookDetails
inventoryID unique identifier for that book
bookID_FK key to books table

You may want to look here. Pretty nice Book db, Table structure is well named and well done. but does not address multiple copies of the same book. The structure is there for doing a generic book, you would need to then incorporate the BookDetails to hold the individual copies.

Solved - subforms not updating properly

It's a many to many relationship so the bridge table ties it to the books True but this is a list of Authors, NOT a list of books. Therefore, you need to use ONLY the Authors to populate this form. There is a similar problem with the books subform but I didn't track it down. I see now, why...

www.access-programmers.co.uk

 

[Pat Hartman]

I agree with George. You might want to consider other media types besides print. Libraries have videos, DVD, Audio books, even Kindall so you don't even have to go to the library to check out the book anymore. Is this a school project? If so, you will have fairly detailed specs. If you are designing for your workplace, where is it today and where could it be next month? It is trivial to include multi-media data NOW but it isn't so trivial to modify an existing app to even just add audio copies of books. What about illustrators? What about genre? Both are many-many. Are you working with First Editions? Do you have to worry about provenance? I don't want to make your head explode with options, but it is better to think of the possibilities early rather than late

 

[Romio_1968]

Thank you all for help

I am taking it one step at a time, based on George relationships diagram.

A title may have none, one, or many authors
An author may have written many books

So in this case I have a many-to-many relationship, using Table_Author_Junction as junction table.
I quite do not understand why George inserted a supplemantary Primary Key in his Junction table. Moreover, I cannot crete the many-to-many relationship between Author and Titles tables without using both Author_ID and Title_ID as PK in the junction table, so here I am a little bit lost.

My next step is the form.
The form mus allow me to introduce the Titles data and as many authors are necessary (0, 1 or multiple).

When I am creating the form, the following structure will be displayed (the default structure):

I will hide the Title_IDD
Now, the problem is with the child
I need it to let me select from a list of author already stored in the database and if it is not recorded yet to allow me to add a new one.
If there is no author, I should choose *** as default value for none.
As well, I need to be able to add multiple authors.
(Example: The Talisman, by King, Stephen and Straub, Peter).

Thank you for help and patience.​

I totaly lost my touch with Access.​

 

[Isaac]

My next step is the form

No.

Your first step is to create all the Tables. Your next step, arguably, is to design Queries - this is the step that will, to a large extent, know how useful and functional your Table design is.

The last step, the fun one that most people want to sprint towards, is the Forms.

 

[plog]

1. Totally agree with Isaac.

2. If the author doesn't exist you should have another form that allows users to add/edit them which is accessible via this form.

3. If there is no author you do not use *** to represent none, you do enter anying and allow the absence of data to represent none..

4. Year is a reserved word and should not be used as a name. Prefix/Suffix it with what the year represents--e.g. PublishYear, YearAdded, etc.

 

[GPGeorge]

Spoiler: etc

Thank you all for help

I am taking it one step at a time, based on George relationships diagram.

View attachment 105741

A title may have none, one, or many authors
An author may have written many books

So in this case I have a many-to-many relationship, using Table_Author_Junction as junction table.
I quite do not understand why George inserted a supplemantary Primary Key in his Junction table. Moreover, I cannot crete the many-to-many relationship between Author and Titles tables without using both Author_ID and Title_ID as PK in the junction table, so here I am a little bit lost.

My next step is the form.
The form mus allow me to introduce the Titles data and as many authors are necessary (0, 1 or multiple).

When I am creating the form, the following structure will be displayed (the default structure): View attachment 105742

I will hide the Title_IDD
Now, the problem is with the child

I need it to let me select from a list of author already stored in the database and if it is not recorded yet to allow me to add a new one.
If there is no author, I should choose *** as default value for none.
As well, I need to be able to add multiple authors.
(Example: The Talisman, by King, Stephen and Straub, Peter).

Thank you for help and patience.​

I totaly lost my touch with Access.​

How can a title have no author? Books don't spring forth whole from nowhere. Well, not until the advent of chatGPT anyway.

If the author is unknown, or anonymous, you could handle that by not adding a record to the Author/Title table rather than fake one.

 

[Pat Hartman]

Here is an example showing a many-many relationship. It shows a different method of displaying "child" records from each side of the relationship. One way uses a subform and the other way uses a popup. Both are correct. Sometimes one method makes more sense then the other so I show both.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[mike60smart]

Thank you all for help

I am taking it one step at a time, based on George relationships diagram.

View attachment 105741

A title may have none, one, or many authors
An author may have written many books

So in this case I have a many-to-many relationship, using Table_Author_Junction as junction table.
I quite do not understand why George inserted a supplemantary Primary Key in his Junction table. Moreover, I cannot crete the many-to-many relationship between Author and Titles tables without using both Author_ID and Title_ID as PK in the junction table, so here I am a little bit lost.

My next step is the form.
The form mus allow me to introduce the Titles data and as many authors are necessary (0, 1 or multiple).

When I am creating the form, the following structure will be displayed (the default structure): View attachment 105742

I will hide the Title_IDD
Now, the problem is with the child
I need it to let me select from a list of author already stored in the database and if it is not recorded yet to allow me to add a new one.
If there is no author, I should choose *** as default value for none.
As well, I need to be able to add multiple authors.
(Example: The Talisman, by King, Stephen and Straub, Peter).

Thank you for help and patience.​

I totaly lost my touch with Access.​

Some additional tables