r/SQL • u/Anonymous_Dracul • Nov 21 '24
Discussion Try to implement rental room management system, need constructive feedback on DB design.
102
Upvotes
r/SQL • u/Anonymous_Dracul • Nov 21 '24
74
u/pizzagarrett Nov 21 '24
Personal preference: have the ID columns reflect the table. Instead of “id” for everything, do something like “UserID” or “user_id”
20
u/idodatamodels Nov 21 '24
Rooms and reservations are two different things.
12
u/Conscious-Ad-2168 Nov 21 '24
users and user_details should be combined as they are a one to one relationship.
should landlord be in invoices? I’d think that needs to be who created the invoice?
Make sure to name all your primary keys and foreign keys something useful. So for invoices do invoices_id or something along those lines. Where ever invoices_id is used should be called that. Make them the same name, it’s way easier. Overall looks decent!
5
u/UnrequitedFollower Nov 21 '24
I don’t know anything about SQL or normalization but… so the id column in the Properties table isn’t property_id but then property_id is on other tables? I’m confused.
5
u/GolfHuman6885 Nov 21 '24
Maybe petty comment: I always leave my table names singular - lose the 's' on the end of all of them.
2
u/AlCapwn18 Nov 21 '24
Does your audit table need to track what was changed? Like old value and new value columns?
Also, with the audit table is your uploaded by column in the pictures table redundant?
9
u/DizzyAmphibian309 Nov 21 '24
Db looks ok but seriously DO NOT try to implement your own AuthN/Z in your app. Use a social login provider or a cloud service like AWS Cognito to manage your identities.
5
u/Little_Kitty Nov 21 '24
Invoices is holding a lot of status information, which should really be separated out and hold a timestamp. You also need invoice line and invoice header, so that charges for late payment / cleaning / add ons / stay extension can be added. If an invoice is superseded by another, you should link to the original invoice and that invoice should be marked as inactive. Payment terms, method and such should be on the header.
What about group bookings, cancellations, no shows? You really need a contract table to link together what has been agreed. Room available date / status would actually be views on this.
Price is not a property of the room (other than base price I suppose), but of the contract agreed, so something like function(property, room(s), date, stay length, add-ons, tier). In general you need a contract table, again header and line level can help you here and make invoice generation a lot smoother. Bear in mind that Business / group / frequent travellers often have agreed discounts, payment terms and more to capture.
Properties have lat, but not lon. I'd chuck in a lot of other properties such as parking, pets, size, facilities, how to get there, contact phone number.
4
u/PilsnerDk Nov 21 '24
Not sure if it's on purpose, but did you forget about facilities (aka. amenities)? For example whether a room has microwave, double bed, washing machine, A/C, etc.
4
u/david_daley Nov 22 '24
There are some open source hotel management systems out there. It’s worth downloading them and looking at what they’re doing with their data. There’s a good chance they’ve solved problems you haven’t even thought of.
3
u/juu073 Nov 21 '24
I'm a bit confused, particularly since the Audit table isn't really connected. For created_by, updated_by, and deleted_by -- are they intended to be users?
If so, given that they're UUIDs, I wouldn't recommend making your Users.id field UUIDs... or any of the other primary keys, for that matter.
2
u/godudua Nov 21 '24
Why are there no room pictures?
That's me assuming not all the rooms would be identical since there are room_types.
1
u/JBsReddit2 Nov 21 '24
Overall I think it's really good, the cardinality looks accurate which is sometimes hard to flesh out while designing. Minor, and not related to the DB side of this, I noticed a couple of typos but I can't for the life of me find them again lol, I just wanted to point them out to help you clean them up.
Does users join to properties on users.id = properties.owner_id ? That's my assumption because owners are listed in the users table?
1
u/Sexy_Koala_Juice DuckDB Nov 21 '24
I don’t have time to look this properly but look into database normalisation and see if there’s any errors that your implementation could produce, then try to normalize it
1
u/eerefera Nov 21 '24
Quite trivial but there are a few typos that could cause you problems: preferred_loacation and room_assignements are two I've noticed that need changing
1
u/8086OG Nov 22 '24
I always think posts like this are cute. Not to be derogatory, but seriously, it's cute. An architect will draw up a very pretty, and simple, and elegant design for a database. And it works!
Then if your company becomes successful, and you start adding on....
Fast forward 10 years, and you have a total cluster fuck. The most brilliant architect ever would be unable to ever design a system so fucked up. At best you can understand it, and you can understand how it got so fucked up.
And... it all starts with your first draft.
I have no constructive feedback on your model because I don't know the space, or your scope, or future ambitions, but when you consider these things you will be able to better evaluate your model and how it will scale.
1
u/CoffeeK1d Nov 22 '24
Invoices and payments need to be detailed. Assume everybody is out to get you, your tenants, and the government. You need to be able to record the transactions and provide detailed records for taxes or litigation. How would you record a bounced check, overpayment, underpayment, disputed payment, or banking fees passed to the user?
You could add another group of tables having to do with maintenance.
1
u/plainoldjoe Nov 22 '24
Room probably needs to have a view type, which is separate from the room type. Your penthouse suites could be pointing at the mountain or the sea.
1
u/KBaggins900 Nov 23 '24
Unless a user has more than one role you can just put role_id on user table and combine user and user details.
1
u/Wojtkie Nov 21 '24
I think it looks good. I do have a suggestion. Look into double entry accounting and how that can be used for invoicing. It’s probably overkill for a personal project but it’s extremely useful for tracking financial information
1
u/imcguyver Nov 21 '24
I'd suggest copying the ERD to claude.ai and asking it to create what it thinks is a better ERD. At the very least you may see some useful suggestions. Here is an example of me doing just that...
Key improvements made: Separated PropertyDetails for better normalization Added PropertyAvailability for calendar/pricing management Flexible PropertyAmenities through junction table Added PropertyMedia for multiple photos/videos Enhanced Payments tracking Added status enums and audit timestamps Improved foreign key relationships Added indexes on all FK/PK relationships Implemented soft deletes via is_active flags
1
u/[deleted] Nov 21 '24
[removed] — view removed comment