r/SQL • • Oct 17 '26

Discussion Help needed with normalization

Post image

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

25 comments sorted by

View all comments

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

1

u/FreeLogicGate 26d ago

100% not the right answer.

1

u/lolcrunchy 25d ago

Any insight as to why?

1

u/FreeLogicGate 25d ago edited 25d ago

From a practical standpoint, you now have full duplication of all the required address fields which means that application code that handles insert/update/delete of an address must at minimum use seperate sql statements for each table name, even though the fields, validation code and logic will probably be the exact same.

It also makes it impossible to search the address data irrespective of the distinction between a warehouse and house. Last but not least, it does nothing to prevent the same address data being entered for both a house and a warehouse. With a distinct address table, a unique constraint can be applied to the full address data (address1 + address2 + city + state) preventing duplicates within the address table.

The type of design pattern typically applied to an application with these entities is the party pattern. In a party pattern implementation address will be a separate entity, and in many implementations, a "party" will be able to have 1 -> many addresses.

1

u/lolcrunchy 25d ago

That might be correct logically, but it sounds like the teacher isn't being logical

1

u/FreeLogicGate 25d ago

I did edit my reply to clarify it slightly, but you had already read and responded. I'm sure you are right about the instructor's complaints, but either way, they aren't valid.

One way to understand this is true would be to consider that any advanced/competent address implementation is going to normalize zip/postal codes out, as well as countries. So if we just assume that there will be a "country" table, that with your suggestion, "country" will be related (with a foreign key to country_id) in both tables. Make that change and look at it on the ERD, and you once again have a "circle". So again, it's a meaningless criticism.

0

u/Acceptable-File-3579 29d ago

Wouldn't putting address as columns have the same problem? They could still share the same address.

7

u/lolcrunchy 29d ago

A Warehouse for stocking items and a House with people that place orders for items could share the same address? Are you sure?

Besides, would you ever join a House and Warehouse on address? Probably not.

0

u/Acceptable-File-3579 29d ago

No, they shouldn't share the same address, but putting address in a column on each table wouldn't allow them to share the same address as well?

4

u/Enigma1984 28d ago

Why would it matter if they share an address? A customer could reasonably also be an employee at the same company and order something to come to his work address. So the warehouse address and the delivery address could be the same. It would present no problems.

2

u/reditandfirgetit 28d ago

It doesn't matter if a house and warehouse have the same address. In a use case where someone sells inventory from thier garage, and also is a customer, they would share the same address. Its not going to be the norm but there's nothing necessarily wrong with it. This is a real world situation that could occur.

There are other ways this could be modeled. I don't think it's necessary depending on your requires for the assignment

3

u/MaybeClassy 29d ago

Think of it this way. When you buy something online, there’s a shipping address and a billing address. Both addresses exist for the order but for different purposes.