What is a good field separator that can coexist with colons?

Question

I am currently migrating a bunch of data from several Excel files of similar formats into a more structured format.

Imagine Tabular data like this in File 1:

Customer | Cabinet : Device : Port | Cabinet : Secondary Device : Port | ...
ACME     | C1      : Dv1    : 12   | C1      : Dv2              : 11   | ...
Foo Ltd. | C1      : Dv1    : 13   | C1      : Dv2              : 14   | ...

And Tabular data in File 2:

Customer | Cabinet : Device : Module : Port | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : 23     : 1    | A       : E                : 1
LLL Ltd. | C1      : Dv13   : 24     : 1    | A       : E                : 2
Weird Inc| C1      : Dv12   :        : 12   | C       : F                : 1

And there are one hundred more sheets, each with its own extra columns like "Slot" or "Subsystem".

Unfortunately this makes two big problems from my point of view:

• Modelling this in a relational database is disproportionally painful
• When presenting the data to a user I have to stick to the old presentation as much as possible (tight schedule, readability, easy adaption for users)

Therefore the presentation looks almost like in Excel. But now the problem:

The column title must be the same for all hundred sheet. My solution was that all tables now have the same titles:

Customer | Cabinet : Device : Port | Cabinet : Secondary Device : Port | ...

And when I migrate "Module: 23, Port: 1" this will be saved in the field "Port" as "Module 23 : Port 1". And it looks like this:

Customer | Cabinet : Device : Port               | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : Module 23 : Port 1 | A       : E                : 1
LLL Ltd. | C1      : Dv13   : Module 24 : Port 1 | A       : E                : 2

In fact I am quite happy with this solution but I found out that this creates confusion. So I think of this as a solution:

Customer | Cabinet : Device : Port               | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : Module 23 • Port 1 | A       : E                : 1
LLL Ltd. | C1      : Dv13   : Module 24 • Port 1 | A       : E                : 2

What do you think? Is • ok as a separator? Have other ideas?

The basic problem for choosing a separator is: the Excel sheets contain in some cases the most awkward characters, it's already amazing that : is used as a separator...

EDIT: An additional note: For reasons of simplicity, but also for consistency I want the headers in all table presentations to look the same.

EDIT2: I don't limit myself to ASCII characters. Any unicode symbol or even images are fine.

Answer 1

Whatever separator you choose, it will be ambiguous since it is missing in the header row (looks like there's a header missing and module is in the wrong column).

Perhaps you could try grouping the subsidiary values e.g.

Customer | Cabinet : Device : Port              | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : [Module 23]Port 1 | A       : E                : 1
LLL Ltd. | C1      : Dv13   : [Module 24]Port 1 | A       : E                : 2

Or better, since the column implies port is primary and module secondary:

Customer | Cabinet : Device : Port              | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : Port 1[Module 23] | A       : E                : 1
LLL Ltd. | C1      : Dv13   : Port 1[Module 24] | A       : E                : 2

Answer 2

It looks like you are limiting yourself to using ASCII characters to present data. This is not a good idea. These days computers are capable of using graphics to present data on the screen.

I can understand that your planning does not allow a more fundamental approach to the problem, but the things you mention (easy adoption and readability) are are in fact good arguments not to proceed with your idea.

If you do want to stick with ASCII characters only you indeed have very few options and I don't think you will find a character that makes this more readable. Your suggested character • is not a bad choice in the given circumstances.

This being said I would like to suggest that you do your best to convince whoever is responsible for your budgets to foresee extra budget. What helps a lot in the convincing process is to make a mockup of what it would look like if you present the data in a modern GUI. Show people that and then show a mockup of the current approach.

Answer 3

I will avoid the discussion of whether this is a good way of representing the data overall as you have already given your reasons for the constraints.

Your solution is an improvement on the version without the • as a separator, but not the best improvement in my opinion.

Before hand you gave 3 headings (Cabinet : Device : Port) but then had 4 items under that. That is always going to cause confusion (as you said it had done). Whatever you use as a separator there it is an improvement.

But if you're giving data such as "Module 23 • Port 1", then why not have the heading as: "Cabinet : Device : Module: Port", giving you something like this:

Customer | Cabinet : Device : Module : Port | Cabinet : Secondary Device : Port | ...
Bar Ltd. | C1      : Dv13   : M 23   : P 1  | A       : E                : 1    |
LLL Ltd. | C1      : Dv13   : M 24   : P 1  | A       : E                : 2    |    

Answer 4

Would it be possible to decode the source for viewing only? Then you could use something unique for a separator and replace it on view with something natural looking like a dash (or anything you want, really.)

If it need to be different than a dash, I'd look for something that is close to an arrow pointing right

Module x > port 16 or something

http://en.wikipedia.org/wiki/List_of_Unicode_characters gives a host of options, I think http://en.wikipedia.org/wiki/List_of_Unicode_characters#Geometric_shapes that gives you ▶, ▷, ▸, ▹ fits you best. Those are common enough to not alienate your users (like something that is really weird) but probably esoteric enough for it not showing up in module names or something. And they are apparently common enough to be available on my system (with no special fonts installed for that.)