May 1, 2021 at 8:45 pm
Hello, friend. Excuse my bad English 🙂
I'll try to explain my question. So, there is an example of database below.
Currently i'm working on a bit more complicated DB, and i have created this example just for better (and fast) understanding of essence of the task.
Imagine : we have some store.. In this store users can order subscription for listening audio, can buy some goods and can order courier delivery. So, there are 3 tables : delivery, goods and subscription. Important thing : the tables are logically independent each other; each table contains unique set of columns.
And we have a table payment with records of user's payments. This table is not normalized.
Could you please suggest a ways to normalize this scheme?
Flat tables is not a solution because it's obstruction of normalization principles.
No triggers or outer software control. I think should be a solution inside database native capabilities.
I wanted also to attach mysql workbench sheme, but did not found .mwb extension is list of allowed file extensions. Compressed to zip and attached.
May 2, 2021 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
May 3, 2021 at 12:02 pm
You're going to have to think through the relationships between all these things a bit more and then create tables based on those relationships. For example, I don't see a customer table at all. I think you'll need one. Then, a given customer can have only one subscription, or multiple? If multiple, you'll need an interim, or mapping, table between the two. Also, I assume a subscription is defined as X number of goods, or specific goods. I'd expect a mapping table there too. Then, for the definition of a subscription, you have payments, so they're not directly related to goods, but subscriptions.
I'd think the same thing about delivery. Wouldn't that be off the subscription, not off the payment?
Anyway, think through those relationships and I think you'll start to see how better to map out the normalization.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 3, 2021 at 12:52 pm
Agreed. And I'd make sure that I didn't use the Payments table as an Invoice table like what currently appears to be happening.
To be honest, this has been done in many well constructed packages. It will likely be much cheaper and effective to simply buy one. They've already hammered out a whole lot of the grievous errors that you'll make along the way. The good ones also have a tax package included or at least available.
If you're hell bent on building one, step one would be to take a really good course on database design. That's not meant as a slam... it's meant as a seriously useful and thoughtful recommendation.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2021 at 12:39 pm
This was removed by the editor as SPAM
May 4, 2021 at 9:42 pm
Thank you for reply, guys! Didn't expected to get it so fast (because not much of topics here, in this sub-forum). I thought it has been abandoned 😀
The sketch above is just a concept, draft. I created new one. Without mistakes (i hope :D) And will try again 🙂
Well, there is a user and user's money account tables. Also we have a payment table.
Don't pay a lot of attention to the details. 🙂 I minimized their quantity.
Condition: user able to buy train ticket or plane ticket at one time. Only one of them.
Each payment record can represent train ticket buying (fk_train_ticket_id is filled),
or plane ticket buying ( fk_plane_ticket_id is filled)
or account refill transaction ( fk_train_ticket_id and fk_plane_ticket_id are omitted, i.e. NULL )
I think the scheme of this DB is not normalized, because next anomaly could have a place :
This is the primary question of my topic. Need an advanced control by triggers or outer software to prevent this situation. I don't like this way. How do you think, is a way to avoid this anomaly by rebuilding DB structure?
Merging train_ticket and plane_ticket tables is impossible because of some reasons. Flat (garbage) tables are also not good for me.
May 4, 2021 at 10:05 pm
Ok... so with that diagram, ask yourself what happens when you want to buy two plane tickets and a half dozen train tickets in a single payment transaction. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
May 5, 2021 at 11:27 am
Yeah, what jeff said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2021 at 9:15 pm
Improved 🙂 But the question still remains
Have 1 money processing table (payment) with 2 FK , but 2 independent spending channels.
Is a way to normalize? Make 2 payment tables? Triggers? Outer software control? Merge spending tables? Some better idea?
May 5, 2021 at 10:45 pm
This is why I suggested taking a class in database design. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2021 at 1:24 am
Why not model like a regular invoice/invoice detail ? You're making this way harder than it has to be.
Once you understand how normalization works and how to apply it, this is an easy question.
May 6, 2021 at 9:48 am
This is a sketch which represents an issue i met in work with more complicated DB project. The real project - is a DB for goods purchasing, collection & delivery service from one country to another. Customers purchasing a goods online in different stores in foreign country, using a service. Purchased goods are being collected in one place and packaged into single parcel. Then parcel with goods inside moving to the destination country.
So i have there same 2 tables - one with goods details ( name, qty, store name, store contacts, photos, description, size, colors, price, local shipping cost and many other details ) and another table with parcel details - weight, volume, qty of boxes, cross-border delivery price, advanced services & packaging spending and other). These tables are completely different (by logic and essence) each other and could not be merged. Each of these 2 tables have subsequent tables.
Between payments for the goods and payments for parcel a lot of time could be passed. Sometimes - weeks. So, each payment could be OR for the goods, OR for parcel. Impossible to pay for all by one single transaction.
Firstly customer pays for the goods and when parcel assembled - he refill his balance to make next payment for the parcel.
One of foreign keys in payment table always is null. And there is an issue - chance to get anomalies.
So , we have 2 spending tables and one table with money processing. Also i need to keep exchange rate details here and lot of another things. Sometimes is very hard to connect the normalization principles with real world tasks.
So, maybe back to sketch ( simplified representation )?
The main thing is a 2 spending tables, which could not be merged into one. And one table with money processing.
Is a ways to avoid this scheme or rebuild it into something better? I think the rebuilding of this simple draft can help me to understand better ways which could be transferred later to the real project.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply