Database abstract layer

--Boundary_(ID_hLcugvV6zJm6d4/iRvGSog)
Content-type: text/plain; charset=US-ASCII
Content-transfer-encoding: 7BIT

Hi



I am facing a common problem which any developer querying the database would
face.



In our web applications wherever we have more than one standard options for
a field, we usually denote them as integers. Say, for gender, we store it as
1 or 2, but we display that as male or female in the user interface. So far
I had been handling this translation in the presentation layer ie., just
before displaying the data in the table, I check if the retrieved is "1" and
then translate it to "male" and so on. But this becomes clumsy when we have
lots of such standard options and when we need this translation in multiple
places in the application.



So now I am thinking of adding an abstract layer which does all the
querying, retrieving and translation; and then return to my presentation
layer, so that if my presentation layer just calls a function in the
database abstract layer with the required query parameters, it gets back a
resultset with the translations. I hope this is a very common problem every
developer faces.



I would like to have your guidance on this. I understand that a resultset
object is not easy to replicate. How do I make these translations and store
them back in an object and return it to the presentation layer?



Or do we have existing PHP libraries which serve this purpose? Could you
suggest me a better way of solving this problem?



Please help me on this.



Thanks

Vignesh.






--Boundary_(ID_hLcugvV6zJm6d4/iRvGSog)--

Re: Database abstract layer

Why not build the options into your data model? Instead of using integers
for gender, use a varchar field that stores 'Male' and 'Female'. You can
ensure data integrity by adding a check constraint to the column.

If the presentation might change depending on context, you can create gender
reference table with columns like short_name, long_name and abbreviation and
use a foreign key to reference it from the main table.

I try to use natural keys (i.e. Male,Female) instead of surrogate keys (1,2)
whenever possible. It makes the database information more readable and
you're not constantly picking through code to figure out what '3' means.

Hope that helps,
Tony


On 11/7/06 2:19 PM, "Vignesh M P N" wrote:

> Hi
>
>
>
> I am facing a common problem which any developer querying the database would
> face.
>
>
>
> In our web applications wherever we have more than one standard options for
> a field, we usually denote them as integers. Say, for gender, we store it as
> 1 or 2, but we display that as male or female in the user interface. So far
> I had been handling this translation in the presentation layer ie., just
> before displaying the data in the table, I check if the retrieved is "1" and
> then translate it to "male" and so on. But this becomes clumsy when we have
> lots of such standard options and when we need this translation in multiple
> places in the application.
>
>
>
> So now I am thinking of adding an abstract layer which does all the
> querying, retrieving and translation; and then return to my presentation
> layer, so that if my presentation layer just calls a function in the
> database abstract layer with the required query parameters, it gets back a
> resultset with the translations. I hope this is a very common problem every
> developer faces.
>
>
>
> I would like to have your guidance on this. I understand that a resultset
> object is not easy to replicate. How do I make these translations and store
> them back in an object and return it to the presentation layer?
>
>
>
> Or do we have existing PHP libraries which serve this purpose? Could you
> suggest me a better way of solving this problem?
>
>
>
> Please help me on this.
>
>
>
> Thanks
>
> Vignesh.
>
>
>
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Database abstract layer

WE use XML here to store the values, however our requirements also add the
ability to use mutiple languages so its a little more complex.

For personal projects I store the various data like this into a
system_option table as text (1,Male;2,Female;) and then have a common
routine that grabs the required bits of info from this table and passes it
thru a framework when creating the appropriate html. The framework is a
series fucntions that get passed the various bits of data (like what the
field type is (date, text, radio etc), the value from the db, the
system_option value for that element and combines it all into an html
snippet that is then shown to the user.

Bastien


>From: Vignesh M P N
>To: php-db@lists.php.net
>Subject: [PHP-DB] Database abstract layer
>Date: Tue, 07 Nov 2006 15:19:26 -0600
>
>Hi
>
>
>
>I am facing a common problem which any developer querying the database
>would
>face.
>
>
>
>In our web applications wherever we have more than one standard options for
>a field, we usually denote them as integers. Say, for gender, we store it
>as
>1 or 2, but we display that as male or female in the user interface. So far
>I had been handling this translation in the presentation layer ie., just
>before displaying the data in the table, I check if the retrieved is "1"
>and
>then translate it to "male" and so on. But this becomes clumsy when we have
>lots of such standard options and when we need this translation in multiple
>places in the application.
>
>
>
>So now I am thinking of adding an abstract layer which does all the
>querying, retrieving and translation; and then return to my presentation
>layer, so that if my presentation layer just calls a function in the
>database abstract layer with the required query parameters, it gets back a
>resultset with the translations. I hope this is a very common problem every
>developer faces.
>
>
>
>I would like to have your guidance on this. I understand that a resultset
>object is not easy to replicate. How do I make these translations and store
>them back in an object and return it to the presentation layer?
>
>
>
>Or do we have existing PHP libraries which serve this purpose? Could you
>suggest me a better way of solving this problem?
>
>
>
>Please help me on this.
>
>
>
>Thanks
>
>Vignesh.
>
>
>
>
>

____________________________________________________________ _____
Buy, Load, Play. The new Sympatico / MSN Music Store works seamlessly with
Windows Media Player. Just Click PLAY.
http://musicstore.sympatico.msn.ca/content/viewer.aspx?cid=S MS_Sept192006

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Database abstract layer

------=_Part_19203_25002897.1162939016708
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

We have 0/1/2 for gender but also have tlkpSex or something like that. It
has the id and the string we want to use, then our queries just make sure
that data.gender=tlkpSex.gender.

Good luck!

Natalie

On 11/7/06, Tony Grimes wrote:
>
> Why not build the options into your data model? Instead of using integers
> for gender, use a varchar field that stores 'Male' and 'Female'. You can
> ensure data integrity by adding a check constraint to the column.
>
> If the presentation might change depending on context, you can create
> gender
> reference table with columns like short_name, long_name and abbreviation
> and
> use a foreign key to reference it from the main table.
>
> I try to use natural keys (i.e. Male,Female) instead of surrogate keys
> (1,2)
> whenever possible. It makes the database information more readable and
> you're not constantly picking through code to figure out what '3' means.
>
> Hope that helps,
> Tony
>
>
> On 11/7/06 2:19 PM, "Vignesh M P N" wrote:
>
> > Hi
> >
> >
> >
> > I am facing a common problem which any developer querying the database
> would
> > face.
> >
> >
> >
> > In our web applications wherever we have more than one standard options
> for
> > a field, we usually denote them as integers. Say, for gender, we store
> it as
> > 1 or 2, but we display that as male or female in the user interface. So
> far
> > I had been handling this translation in the presentation layer ie., just
> > before displaying the data in the table, I check if the retrieved is "1"
> and
> > then translate it to "male" and so on. But this becomes clumsy when we
> have
> > lots of such standard options and when we need this translation in
> multiple
> > places in the application.
> >
> >
> >
> > So now I am thinking of adding an abstract layer which does all the
> > querying, retrieving and translation; and then return to my presentation
> > layer, so that if my presentation layer just calls a function in the
> > database abstract layer with the required query parameters, it gets back
> a
> > resultset with the translations. I hope this is a very common problem
> every
> > developer faces.
> >
> >
> >
> > I would like to have your guidance on this. I understand that a
> resultset
> > object is not easy to replicate. How do I make these translations and
> store
> > them back in an object and return it to the presentation layer?
> >
> >
> >
> > Or do we have existing PHP libraries which serve this purpose? Could you
> > suggest me a better way of solving this problem?
> >
> >
> >
> > Please help me on this.
> >
> >
> >
> > Thanks
> >
> > Vignesh.
> >
> >
> >
> >
> >
> >
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

------=_Part_19203_25002897.1162939016708--