Your SystemSalesman sounds like the Unassociated salesman who are really the same thing. It's just a dummy/placeholder user in the system?

It is a business question for sure but those are the important questions to ask. They should dictate how your data is stored, no? Isn't that the whole point of planning out a schema in the first place.

It's to think of a way to setup your data in such a way that it fulfills your business requirements. From a developer POV it also seems like a nice plus if it happens to make the application code less error prone by either having the database enforce certain things for you and/or by having to write less code.

Whether or not you delete the salesman or set their status to fired isn't important, that is a separate matter.

There are certainly trade offs for any of the solutions. For example allowing null references is easy but then you need to create a null object for the salesman so you can always output a placeholder name and other details because littering your code base with conditionals to make sure the salesman isn't null sucks.

On the flip side if you go with a soft delete then you'll probably end up having to create another UI to handle undoing the deletes which is also more app code and could end up being a space issue if you have a ton of data.

I disagree with the SystemSalesman/Unassociated salesman/null reference approaches entirely.

The modelling on this seems simple. You have Salesman, leads, and salesman have leads. If you want 'unassigned' leads you just look for all leads that aren't associated with an active salesperson.

Maybe with specific performance requirements you need to do something different but that seems like an optimization problem.

My issue is that you're asking business questions in the language of a database. Decide how you want the business to work first. In this case, that means deciding what the procedure is for "orphaned" leads after a salesman is fired, or maybe leads that are brought to the firm by a non-salesman, etc. How that works is not a computer science problem at all. It would be the same problem for the company had it existed back in 1910.

Then after those answers are settled, you can drop down into the language of the database, assuming you decide to implement all this with a relational database at all.

Having said all that, it's likely the solution jj proposed is correct, as it can handle a number of different business strategies, and is simple too.

Active salesman is one of the proposed options above. It's just having a status attached to the salesman. If fired_at is null then the salesman is assumed to be active. That is the same concept as having a "status" field but now you get the benefit of knowing when they were de-activated since fired_at is a datetime.

Btw my problem was hypothetical to show a point that you do need to think about business rules to determine if using null references fits or not.

This is an excellent model of the real world, because in the real world someone does have to make the decision to transfer the leads to a different salesman.

Shoe, sorry, I posted in a rush so I missed that you had that.

So I guess my only disagreement with:

is that I don't consider those leads as orphan leads (obviously just a small/semantic difference). They're leads associated with a salesman who is no longer active. Like RoundTower said that seems like how it works in the real world too.

I can't really think of any time that I'd advocate for null references in an association table.

Yeah, I guess technically they are not orphans since the fired guy still exists in the system but in the business sense they are floating in limbo.

Thinking about this more, I generally look for solutions where you never need to delete or overwrite data because I think they're generally simpler, easier to work with, and can have nice unexpected benefits (by not losing data). So a null association is problematic for me because it means you're probably overwriting data fairly regularly.

Thinking about things I've worked on I can't think of many places where I actually overwrote/deleted data. User preferences is the only thing that comes to mind (since they change often, and there's no real need to store a history of how they've changed).

Edit: I've also never been in a spot where I've had to do any real clean up of data for space reasons. I guess I've either been in the situation where the application is growing really fast and so 'expired' data makes up a small percentage of the total and isn't worth doing anything about or I've worked on an application that just hasn't needed it.

There are certain databases that keep track of revisions so if you do overwrite data you don't lose the old result.

I do like that model too. Just because you change something by overwriting it doesn't mean its previous form didn't exist before or should be impossible to retrieve later.

There was a cool presentation by Erik Meijer that I watched a few years ago where he just ripped a teddy bear into pieces on stage just to prove the real world is mutable. The bear existed 5 seconds ago, so I should be able to bring up the state of the bear before it was ripped to shreds.

You can create a relation that closely models the real world, with a column for an opener and a closer or some variation. The order taker, even on a team, is often not the person who follows up and closes for myriad reasons.

I don't think it is so black-and-white. There's just some things that you don't need to keep. One instance is user passwords. Why keep old passwords?

Sales history should certainly be immutable.

Product pictures and descriptions don't matter and thus can be overwritten on update.

Ah, password reset is definitely an update I've done. User deletion is also something I've done in cases where we were legally required to remove old users.

Product descriptions are a place where my first instinct is to not overwrite it and use versions. Unless you really can't afford the space, why not keep it? You could probably even do some interesting analytics on different descriptions.

Yeah, I can lean either way on that one. Really depends on the goals of the company and the faith in the data. I've found that the argument isn't worth the headache it is bound to cause. I don't think many companies exists where creative and data-heads gel smoothly.

I also have not found enough evidence to suggest the increase in sales from optimizing product descriptions is worth the extra man hours outside of the obvious stuff like having blatantly wrong information which causes increases in returns, SPAMMY HEADLINES, and horrible spelling and grammar. Honestly, I'm not entirely sure if the latter 2 items matter too much after what I've seen lately.

i'm with on dave on this one. for most businesses optimizing descriptions won't matter, and it's not worth the extra complexity that versioning would introduce.

I guess it depends on the specific business but for an extreme example like Amazon it absolutely makes sense. Storage is cheap and you'd be surprised at how effective small changes can be to a companies bottom line.

I am the Amazon presence at my job. Trust me, it doesn't matter like you think it does. Not even close.

I've seen what companies with strong analytics people can do with things like this. Trust me, it matters more than you think it does.

I don't know your business, so I won't say you're wrong. But in lots of cases it is definitely worth keeping and looking at product data and various versions of descriptions.

i'll grant you it's worth it for amazon.

The debate can rage on, but the way Amazon measures and how those measurements affect your sales from the perspective of a seller is very different than how Amazon works for itself. Amazon is also very good a promoting itself and offers many interesting programs to keep the customers coming. Their funnel is world-class. Their affiliate program is excellent. That commercial with the flying delivery thing was genius, false or not.

Ever notice how you almost always have a good experience buying from Amazon in contrast to eBay? Those metrics can make or break your business and they take those very seriously in their algorithms.

There's a lot of other little things happening, but Amazon does an excellent job of obscuring it all. The name of the game is price and keeping your ratings up. Everything else is secondary.

Some of the competitors in our space destroy us and they have awful pictures, terrible descriptions, and blatantly misleading products. It is a very difficult game and we are learning new things all the time. The results are constantly surprising and counter-intuitive, but never once have we found that product descriptions do much either way (though, man, the arguments can get pretty emotional).

dave, none of that contradicts jj's point or has anything to do with it. at some point of scale micro-optimizing everything makes sense -- that's all he was saying.

There's that joke about making widgets for a dollar a piece and selling it at 50c. How do you make a profit? Volume.

That's Amazon in a nutshell. They pretty much offload all the copy, pictures, shipping, etc., to the sellers. By shear volume and chance, many products will have fantastic copy and many of those will rise to the front page, but product descriptions isn't the key to the company's success at all.

I'm kind of meh on this whole debate, but I'll just clarify I'm not talking about anything being a key to success. Simply that in lots of cases the cost of storing historical data + effort to maintain and use that data < benefit you get out of using the data.

Edit: I'll add that a common complaint we hear from companies is that they wish they saved more of their data. And in most cases the data they didn't store is data that might seem totally worthless at first glance.

@dave, no one is saying it is, you're still missing the point.

It is only on the product descriptions where I strongly disagree -- esp. in the case of Amazon.

Of course you want to track other things, but track the really important stuff first, that's all my point is.

Maybe just maybe at the very deep end, at Target scale, working out language minutia is important, but that at the scale in my experience, tossing out old product descriptions and photos is preferred, if for no other reason that keeping those items can cause misguided focus. Fry the big fish first and don't fall into the "this website will save our business mode." If product descriptions are the most important thing in your company, you are either very large or you are ignoring deep problems.

Does that make more sense and line with what you guys are trying to say here?

everything you said has made sense, and i think i agree with it all. it just wasn't relevant. sometimes the dave wall of text is fun, other times it's like, dude, you only needed to say one sentence. the whole conversation started with me agreeing with you that saving descriptions is usually not worth the trouble. then jj was like, yeah but what about amazon. and i was like, yeah it's probably worth it for them. and then you said a whole bunch of **** that was beside the point but basically amounted to "even for amazon, it's a small part of their business," but there was no reason to make the point to begin with because no one was arguing against that. it could still be worth it for them to save old description data for later analysis, no matter how small a part of their business it is. this is really absurd that i had to spell this all out....