Warehouses for books

[Amema]

The situation is this:
We are a lot of people spread across a nation. Each of us have old books, many of which are the same, but we don't really need all of them. To be able to find out which one's we may donate or throw away, we are about to set up a page where we may all add our books.

The books will be in cupboards or stored in Boxes. The cupboards and boxes will be in a Place. Some boxes may have the same id, but in different Addresses.

I have made three tables:
Books
Boxes
Places (may be several addresses in the same city)

In Places I add City, Address, Contact 1 and Contact 2
In Boxes I add BoxID, Type and Comment plus db join Places:Address
In Books I add all sorts of info plus db join Boxes:BoxID

As I forsee that some boxes and cupboards will get the same name/ID, I would like to also add the address of a box in the Book table. Is that possible, as it is a kind of a third relation? I haven't figured it out by myself yet...

 

[achartier]

I would look at it like this:

A place (placeID) may have multiple cabinets
A cabinet (cabinetID) may have multiple boxes
A box (boxID) may have multiple books
A book (bookID) is unique

So a book points to a Box which points to a Cabinet which points to a Place.

but...
Books of the same name may be in multiple boxes and you need to track that.

So you need a book-name database which has the actual name of the book. The book record in the book db (with a unique bookID) points to the entry in the book-name db. So the bookID record points both to the box db and the book-name db.

Now you can see where all the books of the same name are. you can list all the books in a box/cabinet/place, you can also list all the books in a place that are also in another place, etc.

Hope that helps.

 

[Amema]

Yes, thanks. That helped some, but I was apparently not clear in my initial message.

There are books in different places. Place may be a home, a cellar of anywhere really.
Books may be in cabinets in a specific room OR in a box in a specific place.

I guess the most unique thing is, as you point out, the specific version of any book. Some comes in several printings, with or without changes. Still, some copies have signatures and their state will be different, from "mint condition" over ok and used to "broken" which would be info for the Items table.

What about this:
Table Items with actual copies
Table Titles with info on the books, such as author, year, pages etc.
Table Units with info on shelves, boxes or however the books are stored.
Table Places with addresses and contacts.

If we put in the titles with fiels that could count the books automatically, that would be an extra boon. Any way to "pick" the titles from Items table and make it count?

Items will take title, author and year from Titles
Titles will take storage unit from Units
I still have a problem with the step of cascading dropdowns regarding Unit plus Address in a third table - Items.

Or should I have some kind of a fifth table that will only show info from the other tables?

 

[achartier]

I think what you need to do is depict the relationships on paper and show how each entity relates to each other. Then you can design the tables and the foreign keys. There are many tutorials available on how to do this.

Once you have all the relational stuff figured out then the cascading dropdown should not be too difficult.

 

[Amema]

Thank you for helping me, achartier.

I decided to keep it simpler and use initials for adress and town of the Places table in the same field to join to the Items table instead. There will probably only be 10-15 of these. That way I won't need the cascading dropdowns.

Next thing I realised was that it must be possible to add some kind of a calculation in the Titles table to see how many of each edition there are, without filtering and counting. So now I'll search for info to see if I may add that as an afterthought, or if it must be included in the structure from the beginning.

 

[Amema]

And now I realize I'll need cascading dropdowns anyway, and I have found the tutorial but the pages don't look the same as in the description.

First of all, I assume that I am supposed to select element -> datebasejoin, but I'm not sure. No other choice seems likely.
Selecting Data -> autocomplete
After that I'm supposed to see this accordning to the tutorial (http://fabrikar.com/forums/index.php?wiki/cascading-dropdown-element/)



But all I see is this. No mention of any "Watch" or anything like it to select the primary element for the dropdown.



I need some more hints on how to create the cascading dropdowns, please.

 

[Amema]

Solved that problem, but have a new one... (there is this little plug-in called Cascading Dropdowns that you need to download, see)

What I am trying to accomplish is to get the info of Warehouse and Box from the table Boxes - where I join a box to a specific warehouse - to show up in the table Item where I want to link a box to a specific item. But it seems you can only make cascading dropdowns from an element that is specified in the same table, not in another table.

Is that correct? (If so I have to rethink my whole structure again.)