CODEWITHSUNDEEP
htmldatabasedatabase-design
finspin
finspin

Reputation: 4071

Link between database ids and HTML ids

I have the following problem: I'm storing a list of items in the database and displaying them on a web page. I need to assign id or class to every single item on a web page. The most straightforward way would be to assign the same ids as they have in the database. Here is an example:

Database

ID  |  Name
----+-----------
1   |  Apple
2   |  Orange
3   |  Banana

HTML

<ul>
  <li id="1">Apple</li>
  <li id="2">Orange</li>
  <li id="3">Banana</li>
</ul>

The obvious problem is that HTML ids can't start with numbers. So I thought of giving the ids a prefix, for example:

<ul>
  <li id="f1">Apple</li>
  <li id="f2">Orange</li>
  <li id="f3">Banana</li>
</ul>

But this creates inconsistency between HTML and database and makes working with the objects more complicated. To solve this I would have to add a new column to the database:

ID  |  Name     |  HTML_ID
----+-----------+---------
1   |  Apple    |  f1
2   |  Orange   |  f2
3   |  Banana   |  f3

That doesn't seem to be ideal solution either as now I need to enter HTML_ID manually to the database every time I add a new item. And I need to make sure that HTML_ID is always unique.

I'm sure people come across this problem rather often when doing web development. I was wondering if there is a better way to handle this than in the example above.

Upvotes: 1

Views: 500

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52137

Add the prefix on the fly before putting the id into the HTML, and remove the prefix before searching the database. When choosing between cleaner database and cleaner code, choose cleaner database.

Storing the prefixed id the database:

  1. Wastes space in the table for what is essentially a redundant data. Larger data effectively means "smaller" cache.
  2. Since you want to search it, requires an index. This is an index in addition to the index on id which you can no longer use for the query, but is still necessary to enforce uniqueness. All things being equal, every additional index makes INSERT/UPDATE/DELETE slower and can be extra expensive if your table is clustered*. And also qualifies as "larger data", making the cache "smaller".

If your DBMS supports appropriate mechanism**, you don't actually need to physically store the field - the DBMS can calculate it on the fly for you, rendering the point (1) irrelevant.

However, you still need to index it, so point (2) remains relevant.

* Some DBMSes don't even give you a choice for turning-off the clustering (MySQL/InnoDB).

** Such as calculated column that can be indexed or indexed VIEW.

Upvotes: 1

devdigital
devdigital

Reputation: 34359

You certainly shouldn't be stored an HTML id's in the database. What is the issue with adding the prefix at the point you render the page? Of course the other obvious question is why do you need to assign a different id to each list item element?

Upvotes: 1

TimD
TimD

Reputation: 1381

Using the raw database id seems like a bad idea intuitively, since you then can't use more than one table at once without conflicting ids. The answer is just to decide on a sensible way to rewrite the id into something for the HTML. A robust way to do it would be to concatenate the table name and the id, which then means you can't have any conflicting ids. The downside to this is that you put a lot of information about the database into the page, which you might consider a security concern.

Upvotes: 0

Related Questions