Database Structure

[mftruso]

A single Tenant (parish) should be able to manage all of the weddings and users related to it, but not see data related to other tenants. Determine how best to segment this data. Some common schemes. Also determine if there are existing libraries to assist with this in rails.

• single tenancy
• database per tenant
• column identifier (each multi-tenant table has a column with a tenant id)

[JohnRDOrazio]

rather than base access on the parish, I would base it on the couple getting married, because here in Rome for example hardly anyone gets married in their own parish, everyone either goes to one of the Basilicas or baroque churches or they even go outside of Rome to a church in the countryside. The priest who will be involved in overseeing the booklet is the one who will be actually celebrating the marriage, and he isn't necessarily linked with any specific parish

[kas-catholic]

That makes sense.

I think a single database will be more than sufficient for our needs, and we can use some variation of a column identifier for the priest (if a priest account is associated with the couple).

[JohnRDOrazio]

Perhaps we could rename this issue "database structure"?

I think one database would be fine, in which there would be:

1. one table for user accounts (in which one column is a flag for the "type" of user, whether bride or groom or priest); other columns would be Name, Username, Password (I'm sure ruby will let you store hashed passwords? I've looked into blowfish encryption in some projects I created),
2. one table for projects (each booklet would be a new project): initially a project could be associated with a unique url to access the project, which would however be publicly accessible at that url. If the user who created the project decides to create an account, they could associate the project with their account and the project would then be private, and accessible only after logging in. The project would have a number of parameters associated with it: a. Name of the bride b. Name of the groom c. Names of the bridesmaids d. Names of the groomsmen e. Form of the celebration (With Mass, without Mass, between a catholic and a non catholic...) f. Date of the wedding ceremony g. Name of the church where the wedding will take place h. Name of the priest who will preside over the ceremony I suppose there could be other parameters for the project, such as desired format for the booklet (for example size such as A4, A5, A6; margin, gutter, font... Though this may require some experimenting to find the right approach, for example allow to define styles which can be applied to title headings, to paragraphs, to any eventual rubrics which might be included... I suppose we could have some base style definitions which are automatically applied to give an idea, and then allow the project owner to tweak the style definitions)
3. one table for the liturgical texts, with associated information for each of these texts (for example whether the text is associated with the Easter season, grouping together texts that are alternatives to each other for a single section of the ceremony). I believe that we will have to separate the texts for other languages into their own tables, because they won't always correspond exactly with the choices in English. Each languages has it's own particularities as regards the ritual, so this logic will have to be written into the controller itself. The language tables will be fairly similar, but not exactly the same.

[JohnRDOrazio]

reading up on rails, it seems almost like you wouldn't directly create databases and tables, but simply Models and rails takes care of creating the database tables behind these models? Still trying to wrap my head around this approach.

Perhaps it would be useful to create a wiki on the github repo, so as to document each step of the development process, which would help to make it reproducible

I'm reading up on the rails Model / Database approach, and as this is built, it would be nice to create documentation so everyone knows what steps were taken to make it happen

I can pretty much understand the ERB approach in rails, it's kind of similar in some ways to Google Apps script, where Apps script has a javascript templating approach, and you wind up mixing regular html + javascript with the "server side" Apps script through template tags quite similar to the ERB template tags

The intro tutorial to rails explains how to create a blog, and gives examples of Article and Comment models, I'm trying to figure out how to translate this to Models needed for the marriage booklet project

[JohnRDOrazio]

For example, would you create a Model for Users? and a Model for Projects? and a model for Liturgical Texts?

[JohnRDOrazio]

Something like: 1) bin/rails generate model User id:integer name:string lastname:string username:string email:string category:integer password_digest:string where category will be further defined as an enum of ['bride','groom','celebrant'] and password_digest will be the bcrypted form of the chosen password, which should work with has_secure_password 2) bin/rails generate model Project id:integer liturgy:integer bride:string groom:string celebrant:string church:string weddingdate:datetime user:references where liturgy will then be defined as an enum of ['withMass','withoutMass','catholicNonCatholic'], and array fields will be added to the model for bridesmaids and groomsmen, and user will create a relation between the project and the user who created the project. However it would be useful to be able to have more than one user in relation to the project (bride, groom, and celebrant should all be able to access the project) 3) bin/rails generate model LiturgicalText id:integer body:text liturgicalseason:integer where liturgicalseason would then be defined as an enum of ['easter','ordinarytime'] or something of the sorts

This is a very general skeleton of an idea, not yet knowing the inner workings of rails. I'm not yet sure how to handle multiple choices between alternate text options. One step at a time I guess.

[JohnRDOrazio]

What I'm trying to understand now is, when you generate a Model, which in turn generates a database migration, is this process conceived mainly for tables whose data is not defined from the start, but whose data will be created dynamically in the app? For example, the Users table: every time a new user signs up to the website, a record is added to the users table. The same for the Projects table: every time a new project is created, a record is added to the projects table.

However, the LiturgicalTexts table will almost never change: no data is added to it, no data is removed from it. Until there is a reform of the Marriage liturgy, those texts will stay the same. Would this still require defining a Model? Or could you just operate directly on the database and preload the liturgical texts table with the necessary data? (Of course that would mean that, if using for example PostgreSQL, the data would not be a part of the marriage booklet repo unless the table for the Liturgical Texts were backed up and included in the repo under a data backup folder or something)

[mftruso]

is this process conceived mainly for tables whose data is not defined from the start, but whose data will be created dynamically in the app?

no. models are just that, a representation of the underlying data. models are very powerful as they allow you to skip writing sql directly. For example, to associate a project with a liturgical text you could write something like Project.liturgicalText = LiturgicalText.findBy season: 'easter'. you can preload data like liturgical texts in db/seeds.rb

migrations are generated anytime you change the model. the initial migrations will be to create the tables, but additional migrations will be generated as the project evolves and we modify tables and columns. this helps ensure that the database structure is consistent across environments.

[kas-catholic]

Sorry for my delayed reply. Been a little busy this week...

1. one table for user accounts

Yes! I'd propose a schema like this:

• id int, autoinc, primary key
• name string
• email string - assuming we want emails? Maybe this is optional? Should login be username-based or email-based?
• username string - if we don't use email as the username
• role enum (husband, wife, priest) - Rails provides a nice way of doing database enums IIRC. Do we need any other roles?
• password_digest - for Rails has_secure_password

2. project

Yes, and I like most of the columns you've already listed above. Some notes to consider:

• How to store bridesmaid and groomsmen names? Shove them all into one database column? A many-to-one relationship in a separate wedding_party table? (one project can have many bridesmaids/groomsmen)?

3. liturgical texts

Storing these in a database is one option. I think there are probably others we might want to consider. Data inside a database is somewhat difficult to work with - particularly if it's a long-format like liturgical texts. Given that these are static, another option is to store them as text files somewhere. There are probably even things we could do to store them as .html.erb or .txt.erb or something. I think I'll want to see some examples and think about this a little more...

Your bin/rails generate commands are all on the right track - though I haven't looked closely to know if they're exactly what we want. These rails commands are super quick and convenient but a little finnicky if you're not well-acquainted with them.

---

I'd propose we open separate issues for each of these (users table, projects table, liturgical texts) to continue specific discussion there. The user accounts will be fairly quick, and I have some boilerplate from similar projects that can get us moving with the ability to log in. The other two will probably require a little more discussion before we're ready to jump into code, though we might try experimenting with some code and see where we end up.

[JohnRDOrazio]

How to store bridesmaid and groomsmen names? Shove them all into one database column? A many-to-one relationship in a separate wedding_party table? (one project can have many bridesmaids/groomsmen)?

I would store as string arrays, one for bridesmaids and one for groomsmen. Since in a wedding ceremony there can be up to about 5 bridesmaids (typically 3) and up to 5 groomsmen (typically 3), and all we need is their names to print them like on the inside cover of the booklet as part of the wedding party (they don't need user accounts or access to the project), I think string arrays would work just fine.

[JohnRDOrazio]

I've done most of my weddings here in Italy, it's been a while since I did one in the United States, so perhaps you would be able to tell me whether there is just the Best Man and the Maid of Honor in a marriage liturgy in the United States. Here in Italy there are typically three bridesmaids and three groomsmen, and they all sign the certificate of marriage along with the Bride and Groom at the end of the liturgy

[JohnRDOrazio]

Looking at this website of a Catholic parish in the United States, I see they mention both the Maid of Honor and the bridesmaids, and both the Best Man and the groomsmen. Perhaps for English we can have a string for Maid of Honor, a string for Best Man, a string array for bridesmaids, and a string array for groomsmen?

[mftruso]

In the US there is a Maid/Matron of Honor and a Best Man. These are the only two that sign the marriage certificate. The whole wedding party could be huge, 10 on each side potentially.

In many wedding booklets i see there is both their honorary title and their relationship to the couple. Perhaps a Project could have many WeddingPartyMembers

Project 
--------------
hasMany groomsmen: WeddingPartyMember
hasMany bridesmaids: WeddingPartyMember
hasMany parents: WeddingPartyMember

WeddingPartyMember
-----------------------
name: string
role: string
relationship: string

examples:
WeddingPartyMember.create(name: 'Maria Cecilia', role: 'Matron of Honor', relationship: 'Friend of the Bride')
WeddingPartyMember.create(name: 'John Paul', role: 'Groomsman', relationship: 'Friend of the Couple')
WeddingPartyMember.create(name: 'Andrea', relationship: 'Father of the Bride', )

I think the role and relationship could be user defined although we could provide some suggestions like 'Maid of Honor, Matron of Honor' etc.

[JohnRDOrazio]

I think we can try to keep everything as simple as possible, and only define the minimum information actually needed for the project, without making the tables too complex. Since really all we need is the name of the Maid of Honor and the name of the Best Man (if the couple getting married wants them to show on the booklet), we don't really need to define relationships within tables, we just need a string field called "maidofhonor" and a string field called "bestman" in the project table. I don't think we really need to define the whole wedding party, unless you think someone would like the wedding party to be printed on the booklet. I suppose we could leave that as an option. If that is the case, then maybe yes we should start defining table relationships.

[JohnRDOrazio]

Oh I see, you mentioned that some couples actually do like to print the kind of relationship that the Best Man or Maid of Honor have with them. Well if that's the case, then I guess we should foresee this possibility.

[JohnRDOrazio]

email string - assuming we want emails? Maybe this is optional? Should login be username-based or email-based?

As for emails, I figure email could be useful not just for login but for communications and sending notifications. I would allow a user to login with either email or username, as they prefer, because some people are used to using usernames, some people are more used to using their email for login. I would allow for both. 1) Email can be good for verifying accounts (when a user registers to the website, to associate a project with their account, they will receive a verification link that they need to click to activate their account, this helps avoid spam accounts) 2) Email can be useful for recovering passwords (if anyone doesn't remember their login password there can be an "I forgot my password" link that they click which will send a one-time password reset link to their email which they can use to reset their password, this way they don't get locked out). 3) Email can be useful for sending notifications:

• "you have received an invitation to participate in a marriage booklet project from your to-be bride"
• "your wedding is one month away, but we haven't seen you for a while now and you haven't yet completed your marriage booklet, would you like to complete it now?"
• "so-and-so with email nnn@nn.nn just joined your marriage booklet project! (if you think this may be an error please contact the website curators at info@marriagebooklet.com)"
• or any other notification which might be useful for the project

[JohnRDOrazio]

It might be a nice touch to send a "congratulations" to the couple from the marriage booklet website around the date of the wedding.

[kas-catholic]

In the US it's most common to have a single best man and maid of honor sign the marriage certificate.

However, as Mike Truso alluded to, it's also very common to print the entire wedding party - including all bridesmaids and groomsmen in the wedding program/booklet, with their relationship to the bride and groom as in the example below.

Here's an open source wedding program from the US. I actually based mine off this for my own wedding, though I never published my changes. (This program seems a little more thorough than what I think you'd most commonly find.) https://github.com/schuster/wedding-program https://github.com/schuster/wedding-program/blob/master/wagner-schuster-wedding-program.pdf

[kas-catholic]

Might not be a bad idea to collect several samples like this to get ideas for what we're building? I'll start a separate issue where we can track those and talk about them. #17

[JohnRDOrazio]

Seems to me that sqlite is currently being used as the backend database? Perhaps if we're wanting to use arrays, PostgreSQL has better support?

[JohnRDOrazio]

In the US there is a Maid/Matron of Honor and a Best Man. These are the only two that sign the marriage certificate. The whole wedding party could be huge, 10 on each side potentially.

In many wedding booklets i see there is both their honorary title and their relationship to the couple. Perhaps a Project could have many WeddingPartyMembers

Project 
--------------
hasMany groomsmen: WeddingPartyMember
hasMany bridesmaids: WeddingPartyMember
hasMany parents: WeddingPartyMember

WeddingPartyMember
-----------------------
name: string
role: string
relationship: string

examples:
WeddingPartyMember.create(name: 'Maria Cecilia', role: 'Matron of Honor', relationship: 'Friend of the Bride')
WeddingPartyMember.create(name: 'John Paul', role: 'Groomsman', relationship: 'Friend of the Couple')
WeddingPartyMember.create(name: 'Andrea', relationship: 'Father of the Bride', )

I think the role and relationship could be user defined although we could provide some suggestions like 'Maid of Honor, Matron of Honor' etc.

I guess if we want to keep track of role, relationship, firstName, lastName... then a many-to-one relationship probably would work? As also mentioned by @kas-catholic .

I'm trying to read up in the rails documentation about how to handle the hasMany relationship with ActiveRecord: https://apidock.com/rails/ActiveRecord/Associations/ClassMethods/has_many

If we were to use a separate table for the wedding party, with a many-to-one relationship to the single project, how would you go about defining that in the scaffolding? Something like this? I guess it would be similar for the users that have access to a single project, that would also be a many-to-one relationship, since more than one user will have access to a single project:

bundle exec rails generate scaffold Project liturgy:integer brideFirstName:string brideLastName:string groomFirstName:string groomLastName:string celebrantNamePrefix:string celebrantFirstName:string celebrantLastName:string church:string city:string weddingdate:datetime

bundle exec rails generate scaffold User username:string email:string role:integer password_digest:string project:references

bundle exec rails generate scaffold WeddingPartyMember partyMemberFirstName:string partyMemberLastName:string partyMemberRole:integer partyMemberRelationship:string project:references

A few more questions:

1) If we allow to define the kind of relationship that the wedding party member has with the couple, do we want to define it as a string, to allow for maximum flexibility in defining the relationship? Or could we have an enum with a list of relationships to choose from? As @mftruso already asked in fact, I think I like that suggestion to define it as a string, in order to have maximum flexibility. We can have a list of predefined relationships to choose from, allowing however to define any kind of relationship.

2) Would we need to scaffold the User and WeddingPartyMember models? Do we need controllers and views for them? Or would it be enough to just bundle exec rails generate model for these?