Database Design for transactions belonging to two different tables

I am trying to design a way to store historical pricing data sold by both regular users and organizations. I currently have the following database structure (I later consider users and sellers, respectively, names belonging to the user and seller table, where both are able to record sales through the transactions table):

seller
- id (PK)
- name
- size

event
- id (PK)
- name
- location
- date
- seller_id (FK)

transaction
- id (PK)
- brand
- model
- serial_number
- price
- currency
- event_id (FK)

user
- id (PK)
- email
- name
- created_at

Each seller (in the seller table) can have many events and each event can have many transactions occura t it, while an event can only belong to one seller, and a transaction can only belong to one event. All of the seller/event/product data was added in by me from external data, but I am now trying to incorporate a user (from the user table) to product table relationship, so users can directly add products they've sold to the same product table, while bypassing the event table since their sales don't occur all at once under an organized event, as it does for the sellers.

Sellers only make sales at an "event" where they have many transactions, but users will simply make a sale and it will be connected directly to the user table, NOT connected to an "event" where the sale occurred.

My problem is that the "user" and "seller" table are quite similar, and will be using identical data columns in the transaction table, but the event table is not applicable to a normal user. I need a way to separate the logic between a user on my site, and data that I gathered externally (i.e these sellers don't access the site as a normal user recording historical transactions would).

I've tried a few things including:

adding a polymorphic relationship where transactions morph to either users or events, but have had trouble querying seller and user data together
adding a relationship of the user to the seller table, but have to create a dummy "event" that all of their sales go under, when in reality they don't have an event like the standard sellers, they just sell the product.
reworking how event data is stored so all transactions can be directly queried

something like "SELECT * FROM transactions where seller_id = ?" is the goal here, but I can't find a way to morph these two data sources together in a way that makes sense while keeping event data, and simplifying the logic on my backend for users adding data.

Any help would be appreciated, thanks.

DirkZz

5 years ago

Best Answer

Level 22

It all depends really on what you need and expect to need in the future, in the current situation for example you won't know the date of a transaction made by a user since its stored on the event table of which a User will have none. Maybe its not needed but as you mentioned it might be a good starting point to reevaluate what your application needs to do and if the current structure is still ideal knowing what you know now.

Without knowing the rest of the application and the implications each approach might have further on, just some thoughts based on the questions;

You can make the event_id column nullable and add a user_id column to the transaction table. Then a user can have many transactions without having an event. Not a clean solution and you will loose the constraint you have at the database level now and will probably cause problems in places where you access the Event from a Transaction model for example.
You can drop the event_id column and go with the morphable route, but you also mentioned that you ran in to some issues with a specific scenario. Without knowing that exact scenario which you ran into I have no idea if it is insurmountable and therefore a viable option. To query the transaction table you can filter it down by the morpahble type e.g. select * from transaction where transactable_type = 'App\Seller' and transactable_id = 1; when you omit that you'll essentially be query the transactions for both the sellers and the users. This solution will also break the fk constraint on the database level.

This will let you do for example $event->transactions and $user->transactions

You can drop the event_id column and create 2 pivot tables; transaction_user and transaction_event (with a composite unique constraint on the event_id and transaction_id columns) and query through these tables from the Event and User model. Almost the same as the morphable approach and if you are quite sure that you won't have other models making transactions in the future then I would personally prefer this above the morphable way as it is constrained in the database and not linked to direct models.

But then again it all depends on what the application is and does, might just be that none of these suggestions are any good.

Camron

5 years ago

Level 4

I really appreciate the response, it's definitely weird how the data is set up. It turns out the event data is something good to have, but is for the most part only used to identify the dates of the transactions, which I've planned on moving to the transaction data instead anyways. Thank you for looking into each option, and both the morphable and nullable event has given me some problems unfortunately.

I think what I'm planning on doing right now is moving the event name and date information to the transaction table (which comes with some duplication, but that's ok I think) and making it nullable so I can have direct relationships to sellers. My problem currently is when I need to do something like: "select * from transaction where seller_id = ?" since I have to check both users and sellers and then go through the event table depending on if it's a seller or not. After moving event data, I plan on creating a general "seller" table that will contain shared information of both the "user" and previous "seller" table, namely the "name" column for each one, and then having both user and seller have foreign key constrains with a seller_id. I hope this approach makes sense and would love to hear what you think. Thank you for the great response.

Please or to participate in this conversation.