CODEWITHSUNDEEP
sql-serverdatabase
joetinger
joetinger

Reputation: 2729

Adding a new column vs adding more rows system efficiency

I have a table that contains postal codes with their associated salesperson. We are adding a modification were in the end there will be multiple salespersons associated with each postal code. Is it best practice to just add a new column for the new salesperson or to add more rows?

There are roughly 1.5 million postal codes in the table.

Upvotes: 0

Views: 54

Answers (1)

David
David

Reputation: 218837

Neither.

Adding a new column for a new salesperson is a non-starter. You'd have to keep adding columns arbitrarily in order to add new salespersons. This is just a bad idea in every way.

Adding new rows changes the meaning of the data in the table. The table holds postal codes and information regarding those entities. It shouldn't be responsible for anything more than that.

What you're describing is a many-to-many relationship. This would be accomplished by way of a linking table between the two entities. Something as simple as this:

PostalCode
---------------
ID
Code
etc.

Salesperson
---------------
ID
Name
etc.

SalespersonPostalCode
---------------
ID
SalesPersonID
PostalCodeID

Each row in PostalCode represents a postal code. Each row in Salesperson represents a salesperson. And each row in the linking table represents a relationship between the two. You'd add as many relationships as you want. But don't arbitrarily add new domain entity records when what you want is to add more relationships between them.

Upvotes: 5

Related Questions