1

The system I'm working on has a list of assets that belong to different customers of the company. This data is saved on an SQL database.

Removing irrelevant data, let's say the table is like this:

+----------+------------+---------------+-------------+
| asset_id | asset_name | serial_number | customer_id |
+----------+------------+---------------+-------------+

On the application, there is a data grid that displays a list of assets, with the name in the first column and other data in the other columns (serial number doesn't appear in this grid).

Please note that there isn't a specialised version of the software for each customer. This customer's data could appear at the same time as other customers' data.

One new customer has requested that, for their products only,

  1. Each asset's name should be a random number
  2. In the grid, the serial number should appear instead of the name.

The simplest (and sanest) solution would be to set the name of every asset of theirs to be the same as the serial number, especially since they apparently don't care what the name is (and no two assets will have the same serial), but they've outright refused that arrangement.

The second simplest (and much less sane, in my humble opinion) way to resolve it would be to change the SQL query that brings this data so that if the customer ID is equal to that specific customer, it would bring the serial number in the name's place, similar to the following.

select [...], case when customer_id=1234 then serial_number else asset_name end as asset_name, [...]

Of course, the engineer in me is screaming in agony.

I thought it may be better to have a "special needs" table where the customer ID is saved along with a permanent identifier (the company name could change) and the query makes sure to see if the ID is the one matching that identifier, but I'm not sure that this change would be worth it.

Can someone think of a better way to resolve this issue?

George T
  • 295
  • 1
  • 6
  • To clarify for those answering, is this a multi-tenant system? It sounds to me like there's more to this than the technical. – Andy Hunt Mar 17 '15 at 15:04
  • No, the software is mainly used in-company to refer to customers' data. Reports are periodically generated and that is what's given to the customer. However, for me (the only developer) the users of the software are basically like customers and they want to see their data as the actual customers have asked it to be. – George T Mar 17 '15 at 15:07
  • 2
    Yeah, you don't want to be hard-coding that. Have you considered creating a separate table for "configuration" options for customers? Then you could outer join and use a case statement very much like your existing code. – gvee Mar 17 '15 at 15:31
  • @gvee: Thanks. That's what I meant in the second-to-last paragraph. I thought there may be a better idea though. – George T Mar 17 '15 at 15:34
  • @GeorgeT it's not ideal, but it is certainly better than having to change a view definition every time a customer makes this request. P.S. sorry, I did miss that penultimate paragraph! – gvee Mar 17 '15 at 15:34
  • I just realised that it's not possible to do that anyway because of technical limitations, it will *have* to be hard-coded for now... (Replication is used, many subscribers only connect through mobile data, and any schema change requires a reinitialisation, which takes too long) – George T Mar 17 '15 at 15:38
  • @GeorgeT schema changes do *not* require re-inits. If you use `ALTER` statements, this command can be replicated. If you let SSMS do the work, it will `DROP` and then `CREATE`, which is why people fall foul of this misconception. Also, adding a new object does not require re-init either - you basically just add the article and then start the snapshot agent. – gvee Mar 17 '15 at 15:47
  • Let us [continue this discussion in chat](http://chat.stackexchange.com/rooms/22026/discussion-between-gvee-and-george-t). – gvee Mar 17 '15 at 15:48

0 Answers0