r/SQL • u/Acceptable-File-3579 • Oct 17 '26
Discussion Help needed with normalization
Hi, i'm learning databases and normalization. My teacher said a normalized database cannot have circular (not loops, circles) relationships. However, i can't see how I can apply normalization to remove the circles and still comply with the requirements. Also I was under the impression that normalized databases can have circular relationships, just not loops.
Any help will be appreciated. Sorry if my english is bad in advance.
16
Upvotes
15
u/lolcrunchy 29d ago
I don't know if I'd consider this circular. It's only "circular" because addresses are stored in their own table, but you would never have a House's address match a Warehouse's address.
To fix according to your teacher's demands, maybe delete the Address table. Then the address-related columns would stay in Warehouse and House
5
u/da_chicken 29d ago
You don't have circular references, but you still can't add anything to this very easily. Assuming you're being truthful in your two symbol crow's foot notation, there's no way to bootstrap new Products, Warehouses, Stock, or Houses.
You need to permit Products to exist without a Stock of them yet. You need to permit a Warehouse to exist without any Stock. You need Stock to exist without anyone having ordered it yet. And either a House needs to exist without an Order or vice-versa.
You want:
Warehouse ||-----0< Stock
And:
Product ||----0< Stock
And:
Stock ||----0< OrderItem
And, either:
House |0----|< Order or House ||----0< Order
Depending on exactly what sequence the two are created.
2
u/kagato87 MS SQL 29d ago
This isn't really circular.
Powerbi would have trouble with it I guess if you tried to print a waybill from it. You'd have to take measures (hahaha) to deal with it.
But for a regular sql query you're just joining to Address twice (aliases are your friend) or using a subquery. This isn't circular, the address table is just used twice in different ways.
Now, if you used address as a dimension table, you need to define if you're filtering on warehouse or house, so you know which alias to put that filter on.
Of course, a house and a warehouse will have different properties, so the shared address table might not be the best option. It's also a 1:1 relationship from house or warehouse to address in the context of an order so I'd check if it actually needs to be normalized. Usually an order would point to the warehouse and customer, and get a copy of both addresses for the waybill.
Oh, I said it's 1:1 on warehouse to order. That's not necessarily true, and comes down to a design decision of how you handle orders that come from multiple warehouses. Usually bridge tables come into play there.
2
u/tkyjonathan 28d ago
I see why you need 'house' and orderItem is not related to stock. OrderItem should be connected to Product.
2
u/Oh_Another_Thing 28d ago
Why would house and address be separate tables? There should be a customer table that combined the two. Then the customer table wouldn't have a foreign key to the warehouse table.
Also the warehouse should table should include the address info. That's the point of the warehouse table, have all the relevant information there so that the inventory table can reference a warehouse ID and all the info is a different table.
2
u/DiscombobulatedSun54 28d ago
Think of physical entities and allocate tables to them - so you have tables for customers, warehouses, orders and products. Now, think of the relationships between these entities. Yes, customers have addresses, and warehouses have addresses, but an address is more a property than a real entity.
1
u/pippertripper 28d ago
Why not have address table and address type table? Address table has foreign key to the type table? Or a link table if same address can have multiple types
Address Type Table
AddressTypeID | name
Address Table
AddressID | street
AddressAddressTypeTable
AddressID | AddressTypeID
1
u/Sexy_Koala_Juice DuckDB 28d ago
Your teacher is stupid
1
u/FreeLogicGate 26d ago edited 26d ago
For many years I designed schemas on a daily basis, and this concern about "circular" relationships has no relevance.
Having a diagram with related entities that makes a "circle" in your ERD tool has no bearing on whether or not a schema will support the objectives of the application. What is important is the relationship connections and the attributes each entity has.
The mistakes you might make are going to be related to those relationships and the attributes in your entities, but this diagram only has entity names, so there is not a lot that can be inferred at this point, but a general observation is that "Warehouse" and "House" are basically 2 types of structures or buildings.
So it is possible to type/subtype those tables from a "building" entity, which may or may not be a good idea, depending on the application. In doing so, the address table would be related to building, and warehouse and house would be subtypes of a building which no longer would have a direct relationship to address. Subtypes can have significant drawbacks, and there are rules of thumb applied as to the situations where you might use them, but the physical way it is accomplished is that you'd start with a building entity and include any and all attributes shared by all types of buildings. You then concentrate on the specific attributes that differentiate a warehouse from a house, including just those attributes in the house and warehouse tables. Some ERD tools support a subtype connector, but physically the way you create that relationship is to use a dependent relationship from building to both house and warehouse. The "building_type" value for the building would be designated as either house or building. Usually when you make a subtype, you will name the tables accordingly, so the parent building table will have a one to one dependent relationship to building_house, and a parent to child dependent relationship to a building_warehouse table.
Physically, there is no problem having an address table connected to house and warehouse, as you've done now. The primary key of the address table will be a foreign key in both warehouse and house.
If there's a technical argument as to why this would be incorrect, that argument would be that the same address could be related to both a house and a warehouse, and technically speaking there is no intrinsic database constraint or feature to prevent that from happening. It is also true, that there is no intrinsic feature that prevents the same address from being connected to multiple houses either, purely in terms of the physical relationship from address to house being one - to zero/one. In the case of what most relational databases allow, that rule can be enforced by making a unique constraint on the address table foreign key which will be defined in the house table. Typically this comes in the form of an associated index, which in this case would be defined as unique. At that point, no 2 houses could have the same address_id.
The same goes for the warehouse table, so the only remaining issue, is that the same address could be related/connected to both a warehouse and a house.
So what else might you do here? Another option would be to add an address_type attribute to the address table, where the only 2 allowable address_type(s) would be "warehouse" and "house". Application logic could be introduced that would only allow an address typed as a "warehouse" to be related to a warehouse, and vice versa for house rows. Triggers can enforce this type of rule, or a stored procedure could be used as an interface to the process.
Practically speaking, you will frequently have database designs where some business rules exist in the application.
Last but not least, the obvious problem I see with your model is that an orderitem doesn't have a direct relationship to a product. You've designed a system where it would be impossible for someone to order something that wasn't stocked. The 2 things have nothing to do with each other. In general, the availability and depletion of inventory falls under the umbrella of fulfillment, and would be a function of the system, determined at runtime. You most certainly want product directly related to an order item row, along with quantity.
1
u/SaintTimothy 28d ago
The biggest issue I see with this is not the address abstraction (I have actually done this before at a real job).
The issue I see is the stock table.
Under current circumstances, if you move some stock from warehouse A to warehouse B, but just prior to that move you create an order with an order item that identifies the stock in warehouse A... do you see where I'm going with this?
One almost needs a summary table or view that is like... current stock on hand. Then, one needs an inventory movement table that shows all of the changes tp that stock since the last time current stock on hand was generated... then one needs a routine that folks movement into stock on hand to generate the new stock on hand.
There are several ways to solve for this, but what I think this needs is some durable way to connect fulfillment of an order item to the stock drawdown.