Database Setup add list of dates

[toff]

Hi Bubblers, Happy New Year.

So I am working on a little project for organising my watch collection, and am currently trying to figure out the best way to set up the database.

There will be several users, some of which will have the same watches but will have private info about them such as price paid, price sold, and which watches have been worn on a particular day.

Current Database Setup:

WATCHES
(this will have many fields about the general specifics of a watch, make, model, size etc)

USER

WATCH COLLECTION

I am trying to set it up so the user can select a date from a date picker, select a watch from a dropdown and press a button and add that date to a list of dates within the ‘WATCH COLLECTION’ Table.

Having trouble getting this to work. Any Ideas?

Many thanks.

[joseph.farinhas]

Hey There.

Are the entries for watches user specific? Or will it be a general collection that everyone can edit/view? I might have a better idea to structure this.

[toff]

Hi Joeseph,

Apologies I probably didn’t explain myself very well.

Watches are no user-specific (kinda hoping over time as entries grow you can use this to search for new watches. And also this way can keep track of how many people have the same watch in their collection.

Many thanks.

[joseph.farinhas]

Ok so I would structure it like this

Have the user type not have any watch fields, not needed IMO.

Create a new data type time-worn with data fields;
user data type - single entry
watch data type - single entry
date worn - single entry

Then under watch collection keep the user field. Watch will become single entry instead of lists. and Times worn will no longer be needed.

This way the user creates watch collection entries, each individual entry for each watch they own. and can create individual times worn entries for each watch.

After reading this through it does seem like more DB and a more convoluted way of getting it done but it provides the best flexibility. I have bad experiences with lists of items when trying to reference them for searches etc. With this setup the user can also use a date range to see what watches they wore on those days etc. Hope it makes sense.

EDIT: Just to reiterate

There will be 4 total DB’s

User- by default you need this
watches - community created lists of watches available in the world
watch collection - an entry for each watch the user own, assigned to him/her by the user field
times worn - an entry for each time the watch was worn.

[toff]

Hi Joseph,

Thank you for this, it makes sense. I actually started down the ‘time-worn’ data type route earlier then I second guest myself.

Would this work with:

Create a new data type time-worn with data fields;
user data type - single entry
watch data type - single entry
date worn - list of dates

I am hoping to add some charts for how often each watch is worn. And do a calendar style repeating group that will have pictures of each of the watches worn on each day.

[joseph.farinhas]

For some reason im forgetting why i scrapped the whole lists of dates in all my apps. I strictly make new items for each date. But you can try it and see. Maybe was for something that didnt work 2 years ago and now it does. I believe it had to do with nesting repeating groups but my mind is drawing a blank

[joseph.farinhas]

@toff i believe if you plan on using the full calendar plugin which is what I use, i think thats why you need to have seperate “event” dates. not a list of dates.

[toff]

Can you have images displayed in that? I haven’t used it before.

[joseph.farinhas]

The calendar has a text line for “event caption” . You can either type something in or use a dynamic field from the data type. For mine, in order to get this result

Title
Subtitle
More info

on seperate individual lines i had to create a new data field called calendar caption and when creating the thing, i would format it using the regular bubble text field. Weird work around but it works. With images im not sure but maybe something similar can be done using html.

[toff]

Awesome thank you.

OK so having the DB setup like this has fixed one of the problems I was having (being able to successfully update the list of dates of a specific watch). Now I just create a new thing each time.

But now with them being individual entries, my bar chart showing a count of what watches are worn the most wont work. Any ideas of the best way to count these with them being individual entries?

[joseph.farinhas]

@toff I would do a search for worn-watches with the constraints user = current user and watch=whichever watch data you are using and then using the more… option to add the modifying comment :count

The graph is looking for a number, not a list of worn times.

[keith]

You’re kind of missing a piece here and thinking about this wrong. What you’re currently referring to as a “Watches” is just a single Thing, a “WATCH”. And, in fact, it would be better referred to as a “Watch Detail” (or, even more simply, just a MODEL). Actually, let’s call it that.

And there’s no need to put a list of Watch Collections on a Watch Detail (Model) because you’re thinking about it wrong.

The next piece you need is a “Watch”: A Watch represents a specific instance of a Model. It’s exactly the same thing as the relationship between a Product and a Line Item in an Order (if you were building a store).

A Model is a representation of an ideal instance of some watch. Like a “1983 Casio Calcmaster 2000”. A Watch lets us differentiate between your 1983 Casio Calcmaster and my 1983 Casio Calcmaster.

And then a Watch Collection, is just an object with a list of Watches on it. (Watch Collection has one custom field on it, “Watches”, which is a list of type Watch.)