Support the ongoing development of Laravel.io →
Database Eloquent Architecture

Hello, I'm new to Laravel and PHP, but I really enjoy working with it :)

I want to post, edit and delete data in a MySQL DB with Eloquent. I have a DB with 2 tables: "userdata" and "tdata". Note that "userdata" is not a table with userinformation like username and password. My problem is, that both tables have a primary key, consisting of 4 Integer fields. But Eloquent dosn't support this. So which is the best workaround? I saw an issue on GitHub with a workaround, but it modified the source, which I don't want to do. So what could be a good workaround?

Some background information about my DB: I have a desktop application which is used to track training progression in a certain sport. Now I want to build a web application. I also want to add synchronization. So I build my MySQL DB like my existing SQLite DB (which wasn't the best idea I think).

My two tables look like this: "userdata": YID | WID | some data about the athlet (eg. weight, name). "tdata" : YID | WID | DID | ID | some data about the training (eg. duration, ...).

Primarey key of "userdata" are the two ID fields and the prim. key of the "tdata" table are the four ID fields. These fields hold the following values:

  • YID: The year as Integer (like '2014').
  • WID: The week of the year as Integer (like '46').
  • DID: The day of the week as Integer (like '6' -> Saturday).
  • ID: The actual ID of the training session as Integer (like '2' -> second training session of the day).

This looks wired, because the first 3 indetifiers could also be stored in a format called "Date" :D This structure was created due to my laziness. I have a view in my desktop application which shows all training sessions in a sidebar (treeview). This is far more comfortable because it looks like this: 2014 -> 46 -> 2 -> 1 -> 6 -> 1 -> 2

Actually numbers are replaced by daynames: 2014 -> Week 46 -> Tuesday -> 1. Session -> Saturday -> 1. Session -> 2. Session

So I just need to get that data from the "tdata" table. If my table would hold DateID and ID as it's primary key, I have to deal with some math.

The "userdata" is also a relict of my laziness. A athlet needs to save his weight for every week. So instead of saving the weight for a week in each record of the training data table, I created an extra table holding this information and including the name of the athlet. The "tdata" tables foreign key is set to the "userdata" tables year and week identifiers (YID and WID).

So that's the story of my messed up DB. I really want to build my DB from scratch again (and even do some major changes in my desktop app), so can someone give me idea, what could be a better structur for my DB?

On of my issues is, that I have never heard of "Relationships" before, like explained here. They look useful, but I don't fully understand them. Are these relationships a SQL thing or are they just a class(?) in Eloquent/PHP. The discribed DB is the only SQL DB I have/use, some I'm not an SQL expert either.

Thanks, Mic.

Wow I looked through my post again. Wall of text. And please excuse my poor english, I hope I didn't make to much mistakes.

Last updated 2 years ago.
0

I'm sorry, I can't edit my post (won't save). Here are two links which show my structure explained above:

Last updated 2 years ago.
0

In 95% of cases in SQL DBs it's good to introduce an artificial "id" (integer sequence) primary key even if some subset of your fields is unique. It's far better for data handling - as you probably noticed ;).
You have mentioned your "laziness" - I'd say - refactor the data structure, because your problems may only stack up in the future.

Last updated 2 years ago.
0

Thanks for the tip!

szegad said:

You have mentioned your "laziness" - I'd say - refactor the data structure, because your problems may only stack up in the future.

Yeah, I plan to do that. But I think I need some more learning. As I said, I struggle with creating a useful and logic structur for my purpose

Last updated 2 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

LukasMa lukasma Joined 26 Nov 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2025 Laravel.io - All rights reserved.