2

Direct Question:

What are best practices for design of database tables for storing urls configured on a page.

Our use case:

We had a design discussion and were unable to conclude on a what is the correct design. We are writing a page with URLs embedded in a table. So the page looks like

 ....content(some text)...
 a     |b     |c     |d
 text  |url   |url   |url
 text  |url   |url   |url
 text  |url   |url   |url
 ....content(some text)...

The idea is to make these urls configured in database so that url changes does not need a deployment every time it url changes.

EDIT> a,b,c,d are table headers. While the content under section a will be a normal text the content under b,c,d will be hyperlinks.

Given all this what should the table design for this structure. We considered two schemas:

(a,b(url),c(url),d(url))   #dependent on table design. advantage: written code would
                           #be very simple and would not be dependent on the number 
                           #of entries in the table.(a simple for loop would 
                           #suffice for presentation logic). Disadvantage: if table
                           #changes in future, this table is doomed.
                           #EDIT>a,b,c,d are place holders to represent that content
                           #represent the headers of table.
(id, url)      #advantage: Generic enough to encompass any url.
               #disadvantage: This would require presentation layer changes in 
               #case of new addition of new row.
               #EDIT>id is just a number to identify this url and would be used to 
               #refer while identifying this from presentation layer.

We were unable to conclude which one of this is better to go with as each have its own tradeoff(until we have missed something). Or none of this is good.

We are using a NoSql Store and Jsp to write the frontend(presentation) logic.

EDIT> The following can be the type of changes that can happen to the table:

  1. addition of a new row(frequent).
  2. removal of an existing row(frequent).
  3. Order of columns can change(but rare).
  4. Number of columns change(very rare, do not think ever happened but can happen).
  5. Change of underlying URL(inherently supported in both designs, so not important).

The main concern here is maintenance overhead(both in presentation and back-end perspective ) that will be caused by any one of the design considered.

EDIT2>

So this project is only about writing front end for the already existing services. We do not have to deal much with so called application state. But on a certain web-page there were a few URLs(embedded in the table which I mentioned) and business requirement was not to deploy (this piece of code) every time someone has pus a change request(like change of existing url which is the most frequent type of change). So all the information on URLs is to be moved to a database and is to be queried on page load(or may be pre-load so that we do not screw the performance of page) from the webpage. Now this discussion was about designing a appropriate table for this use.

Aman Deep Gautam
  • 8,091
  • 21
  • 74
  • 130
  • Explain "storing urls". I have no idea of the role of your schema tables. Is the schema table some of the input to the presentation code that, along with other input, will be displayed? Or is the code using the schema to store its state while it calculates, and it just wants to move around ids instead of strings? Or is the schema table used to store the output of the presentation layer? Or what?? Explain the something/state that the presentation layer is mapping to graphics and if necessary any relevent state of the presentation layer that it stores while it is calculating or when it is done. – philipxy Jun 07 '14 at 09:19
  • @philip By `storing urls` I meant that the url which are to be embedded in the page are to be stored in database. Yes the schema of the first table will be input to the presentation code but in the second case it only stores url and everything related to presentation will have to be static content on the page(In the first case it came from db table). Hope this helps. – Aman Deep Gautam Jun 07 '14 at 19:21
  • All your edits and comments have just explained some detail more or less in the original question. You still have not answered my questions and you are still not making distinctions about states of the application layer vs the presentation layer. _What would help is to (try to) answer the questions I asked._ Now I have extended and revised my answer. Good luck. – philipxy Jun 08 '14 at 10:09

1 Answers1

2

TL;DR:

A presentation layer presents a something: (part of) the state of an application layer. Design application state independently of presentation.

Anticipating change

Minimize the impact of changes to the application layer on the presentation layer (or other users) by offering a state (database/api) that hides information. That means prioritize likely changes and offer an interface whose secret knowledege is which variant is current (pdf).

Eg the following apis become more generic but allow more & more change going down the list: "dog", varchar(3), varchar(n), list of char, string. The corresponding code might be: print "dog", for i=1..3 print s[i], for i=1..n print s[i], for s.iterator() print s.next(), s.print().

The bigger the changes the more generic the access. So find an engineering balance between likelihood and size of change vs obscurity and calculation costs of genericity.

The application layer transcends presentation

Consider a board game. There are players and scores and turns and rules and locations on the board of which some are where the players are etc. But boxes in stores can have different graphics, different physical materials, different tokens, different text format, different languages; some text or graphics won't be translations but analogues ; a video version has its own presentation in light with commands replacing actions; a networked multiplayer version has such multiple presentations simultaneously. Separate presentation from the state of the game.

First properly design a database/api for application state (the "game") independently of any presentation (a "box"). Embody it in the DBMS. Then a presentation queries/accesses that state/api. Decide on url text vs ids in the application state specifically assuming nothing about presentation (what will be displayed or what the format will be or when). (But you have not responded to my comments re this.) Presumably there is either a table a-b-c-d in the application state or there isn't but it can be expressed as a query of other tables that are. Decide on those tables and any others specifically assuming nothing about presentation. (But you have not responded to my comments re this.) That design should hide which of anticipated variants the current application layer actually is.

Then write the presentation layer to query/access that application state.

The presentation layer's implementation will have its own state. It can put any of that in the DBMS or any other resource. (But you have not responded to my comments re this.) Don't confuse the application's use of the DBMS with the presentation layer's use of it. The DBMS is just serving two masters. Even if its layer decides its state puts application and presentation tables into the same database.

...as much as possible

Application state/api design is always based partly on what queries/acesses are to be made and on the architecture. Relational databases minimize this for appropriate applications. In a non-relational implementation, design will be more affected by the particular queries/accesses of the presentation layer but also all users of an application.

The answer

As much as possible, do not put a-b-c-d or url ids or other application state in the database for reasons of the presentation layer. Design the application layer state/api independently of the presentation layer. That should leave you able answer your question in detail. (But you have not responded to my comments re this.)

philipxy
  • 14,867
  • 6
  • 39
  • 83
  • I cannot understand what information do you need? Could you please specify what type of information you need? I would be more than happy to provide the same. I have edited the question to fill in some more details. – Aman Deep Gautam Jun 07 '14 at 07:26
  • Thanks. Re rows: When someone sees a row with values (a,b,c,d) in the presentation table, what does it tell them, in terms of a,b,c and d? When does a row of values (a,b,c,d) appear in the 1st schema's table, and when not? (Probably same as the presenation table?) Ditto for a row (id,url) in the 2nd schema's table. What does a row of each table _mean_? I _gave_ a guess of the sort of thing a row says. Another: "in the presentation code value [id] identifies string [url]". What _do_ your rows say? Collectively they describe the state of something. My comment above asks about that. – philipxy Jun 07 '14 at 09:32
  • As stated earlier, `(a,b,c,d)` are headers to table which can be like `(name, email_address, website_home, blog_link)`. So a row would be `(aman, aman@abc.com, aman.com, aman_blog.com)`. Now the entries can be added or deleted correspondingly(new persons are added and existing are deleted). Similarly links to website and blog can change and so does the email address for an existing person. – Aman Deep Gautam Jun 07 '14 at 19:25
  • Thanks for the edit. I will explain some background to give you the context asked. Please see the edit in question. Hope this time I answer your question. – Aman Deep Gautam Jun 08 '14 at 18:34
  • I rolled back your edit to my answer. You misunderstood. The "print" paragraph is a list of _five alternate api designs_ and a list of _an example call under each_. – philipxy Jun 08 '14 at 21:39
  • I understood that but thought that it was better to have them in more readable format. Anyways does the edit which I made in question answered what you were asking? – Aman Deep Gautam Jun 09 '14 at 03:41