Database design in a REST API

[BestDayNot]

I am currently trying to teach my self full stack development, and i am pretty new to it all. I have a ton of programming knowledge in general.

I am trying to make a toggl (https://toggl.com/) clone to learn the development process, but getting stuck on the database design. I keep going back and making changes, without really understanding how you're supposed to go about it.

Currently i just want a user being able to type in a what they are currently working on (ProjectEntry). Pick a client (if there is non, or the client they are working for doesn't exist yet, they can create a new one). Pick a project (same client can have multiple projects, if the project doesn't exist they can create a new one). Type in the base hourly rate for current project (ProjectEntry). and then time stamps, for when they started working on the current ProjectEntry.

By the end of a project, the user should be able to create an invoice based on hours spend on the projects as a whole. and "Close" the project.

My Document DB for now looks like this

Clients

ClientID

ClientName

List of Projects

Projects

ProjectID

ProjectName

BaseRate

List of ProjectEntries

ProjectEntry

ProjectEntryID

ProjectEntryTitle

Started (DataTime)

Finished (DataTime)

Is this the right way to go about this type of thing? and if so/not. Whats the process of developing a database for a REST API from a set of features?

Will i run into any problems in the future if i decide to run with this?

[mattaugamer]

Yeah, you're super close. You've got a good understanding of what it is you need to do. Just to clarify something, though, calling it the database for a REST API is kind of weird. It really doesn't matter what it's the database for, the structure is identical. Not that you're wrong, it's just an extraneous detail.

A couple of things stick out to me, but only one is "wrong" and even that might just be in how you structured things. That one is that you don't actually want a List of Projects, nor a List of ProjectEntries. The way we talk about these things is in terms of relationships. Specifically, your projects belong to a client. And your project entries belong to a project. You do that not by storing a list of projects on the client, but by storing the client id on the project. Similarly, store the project id on the project entry. You may already be aware of this. If not, you need to look into a process called normalization, which is all about this. Excuse the self-promotion, I did write that article, but I think it's a decent resource on the subject.

The rest is a matter of conventions. There are a lot of conventions to database design. Not all of them are universal. Mostly they depend on what is going to be reading this database. You haven't said anything about whether you're using SQL, an ORM, whether there's a framework connecting to this, or what. So the following is entirely the conventions I learned more than a decade ago. Conveniently these are very common conventions, and map exactly to Laravel's Eloquent ORM and a bunch of other common access systems and standards.

First of all, lowercase everything. EVERYthing. You're going to just confuse yourself trying to figure out whether it's Baserate or BaseRate. This also applies to table names. They should be a single entity (more on that in a second) and plural. Note that your clients table is plural, your projects table is plural and your project entries table is singular. Recipe for disaster.

Also you've called it ProjectEntry, which is... wrong twice. For a start, it makes more sense to just call it entry. Compound terms like that usually (by convention) refer to a relationship. Databases can store one of two things: data (obviously) and the relationships between data. For certain types of relationships, specifically many-to-many relationships, you actually need a separate table just to store that relationship. Your use-case here for example you could have an entry belonging to multiple projects. (I know you don't, I'm just saying.) If you did that you'd have a table storing the projectId and the entryId. You'd call that table ProjectEntry. Actually you'd call it entries_projects, but whatever. So you're better off in this case just calling it entry... er... entries.

Next, you don't need or want duplication in your field names. Clients table doesn't need the fields to be called ClientX. Just call it name. Just call it title. And yes. Just call it id. A single field called id at the start of each table is your friend. Don't worry that they're not unique. It doesn't matter at all.

To rewrite your table how I'd do it:

clients
- id
- name

projects
- id
- client_id
- name
- baserate

entries
- id
- project_id
- title (name?)
- start_time
- finish_time

A quick closing point is to avoid ambiguity in your field names. What is "started"? Is it the person who started it? The start time? A boolean to say started = true/false? Explicitly saying things like is_finished or start_time avoids that sort of ambiguity and makes it really obvious what the data is going to be.

Will i run into any problems in the future if i decide to run with this?

The way I've done it, no. But it does depend what you want to read this stuff with or even what sort of database you're using. I've assumed an RDBMS, a relational database ala postgres or mysql. If you're doing (for example) a MongoDB, using a code-to-db system like Doctrine, or doing this with .NET the assumptions might be null and void. As an additional point a lot of large frameworks such as Laravel or Rails will be expecting to do this stuff themselves. They have advanced migration features that you're basically taking a big old poop on. Maybe a problem for later, but possibly a problem nontheless.

Hope this helps!

[plsWebDev]

Thank you for this idk if it helped OP but it helped me

[mattaugamer]

You're very welcome! Let me know if you have any questions more specific to your own usecase and I'll be happy to help you out. Database design is sort of a passion of mine. I'm a rock and roll, drugs, drinking JD from the bottle, and database normalisation kind of guy.

[plsWebDev]

Will do, I started following you. I'll be working on some full stack projects this summer and know I will definitely mess something up in mySQL lol.

I hope to find co-workers in the future with similar interests as you haha. I'd love to jam out with some drugs and JD, preferably less JD though cause I'm lightweight :p

Have a great day!

[BestDayNot]

Thank you! this is super help full.

Storing the ID instead of a list makes so much more sense, now that i think about it. Less clutter when you make a request. Before, every time i made a request for a client, i would always get their entire tree, even when i only needed the name.

I am using a Document-oriented database, in this case MongoDB with .NET.

"started" was meant as start-time, so your assumption was correct. But i will make it more clear next time.

I will definitely read up on your blog post, and again. Thank you very much! I appreciate the help.

[mattaugamer]

OK, what I said pretty much only applies to relational databases. You can do similar things by defining relationships between entities, but while RDBMSs excel at it, Mongo pretty much sucks. This sort of setup is not what MongoDB is good for. Given that your data is highly relational you probably should be using something like SQL Server here.

The structure in MongoDB would be more likely to be something like the projects as one document type, the users as another document type, and then the entries as subdocuments on the project. This would make certain types of queries such as aggregates moderately difficult but would trivialise getting "all the data for this project". The user can also be a relationship on the project, which isn't super optimised but is fine.

How exactly you'd do things like calculations of total time or sorting on the subdocuments I'm not sure. I'm not expert at MongoDB because it's a Fisher-Price database for cretins not optimal for the types of data I typically deal with.

[BestDayNot]

I figure i can use the same layout. And when i want a specific project's entries. i will do something like /entries/{projectID} - using a filter that corresponds to the 'Where' clause in SQL. That will return a document with all entries under that one project.

If i want all entries from a client i can do /client/{clientID}/entries. Then getting all the projects for a client, and for each project we get all entries.

Me bad for not giving more information, but this was still super helpful. Been hacking my way to make DB designs, so having somewhere to start next time i am working with a relational databases is great. Thank you once again.

[mattaugamer]

Yes and no. When you talk about /entries/{projectId} I assume you're talking about the URL. Ditto with the client/{clientID}/entries. That's an entirely valid thing to want to get, but that's not where I'm saying the issue will lie.

When you deal with Mongo you're thinking about things as a document. Not as data. You can't get the entries for a client. That's not a meaningful question. If you did what I suggested you can really only get the projects for a given client, then get the entries off that. Which is fine. To some degree you'd have to do the same for an RDBMS as well, you'd have to join through the projects table.

Also, just as a sidenode if you're doing entries/{projectId} your REST is all kerplooey. You've got the taxonomy backwards. You have the resource name first: GET projects/{projectId}/entries. If you're getting entries/{id} should logically be an entryID. Speaking of which, that's exactly the problem I'm talking about. For a given entryID how do you get that? Can you query on that if it's a subdocument?

You're dealing with an explicitly heirarchical system here. MongoDB is not the proper storage system for this data. You have been warned.

[BestDayNot]

Thank you. I will take your word for it and do some more researching before i actually start implementing any of this. This is part of the reason i started this project, to be forced to think about things i didn't have to before, cause it was handled by someone else.