Create a system for adding time and date when booking a vendor

[KLSharkey]

Problem / motivation

If we do this, we will need to figure out how to connect the vendor and user to the time and date on the back end and how it will be set on the front end.

Proposed solution

Possibly another table? Using JSON objects? Not sure of the solution yet.

[seanmstevens]

I was doing some research, and it looks like pretty much everyone agrees that storing JSON in a SQL database is a bad idea if we're accessing the data regularly (it leads to some bad performance problems).

However, it doesn't look like it'll be too hard to make an events table in our database schema with columns for the event id, name, time, organizer id as a foreign key, and the vendor id as a foreign key. We can also add other data columns to the table as we see fit. The only issue is that the vendors and organizers have different relationships to this table. For organizers, it's a one-to-one relationship -- one organizer is associated with one event, and that event is only associated with the organizer that set it up. For vendors, it's a many to many relationship, because there can be multiple vendors for an event, and a vendor can be booked for as many events as their "calendar" will allow (theoretically).

It will be kind of tricky to keep track of all the relationships and getting it coded into Python, but once we have it figured out and our models are working, I don't think it'll be difficult to actually add events to the database. Plus, this is much easier to query if we just want to get individual properties of an event without having to pull the whole JSON object every time.

If anyone has any other ideas, I'd be super interested to discuss them!

[KLSharkey]

Would the event table essentially take the place of the association table? I think we might be able to make that work. Let's discuss in class tonight.

[seanmstevens]

I think we can probably integrate both of them into one table, called an association object. SQLAlchemy's documentation says that it's used when you need an association table that includes more data columns than just the foreign keys. Here's some more info: http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object

[KLSharkey]

That looks very doable. Tonight we can figure out what all we want on there and then I can start working on it.

[CatherineEvelyn]

I thought the user (or "organizer") table will have the event data in it, why do we need a separate event table then link it to user table as one-to-one? Wouldn't it be better if we just use one table for "user" and "event"?

I haven't looked into our current database but let's say user table has primary key (id), name, email, address, state, zip code, wedding date, number of guests, foreign key (to vendor table). Then vendor table has primary key (id), company name, contact person name, email, phone number, address, state, zip code, min price, max price, vendor category, foreign key (to user table). The two tables will be many-to-many (one user will book multiple vendor, one vendor will be booked by multiple user).

Wouldn't that be simpler than having 3 tables (separating the user with it's event)?

[KLSharkey]

We have to have a third table to keep track of that many to many relationship. Even if we habe wedding date and so on in the user table, we would habe no way to connect them to multiple vendors and the vendors, in turn, to multiple users. The third table would track those relationships.