Designing a generic database API

Hi,

My current project requires me to convert a mysql based software to a more generic one. I started by designing separate db class files and separated the lower level connection queries from the business logic. By doing this, I now have mssql.class, mysql.class, sqllite.class etc..

But am not sure how to handle sql functions in queries. For instance, one of my queries need the use of a date function to add minutes to a db field.

In mysql, I accomplish this using

dbfield+interval '$arg' minute between date1 and date1

But in mssql I cannot use this type of query. It seems I'll have to use date_add() function. How do I handle this situation?

My frontend scripting language is php

Thanks d'advance
CeliaI would create a function in your PHP code that takes two arguments, a "date" and an "adder" to the date. That way you can "cook" them as needed for each database engine, while keeping the PHP quite generic.

-PatP

Design Question re images

Hi

I have a table of people, and for some of them I want to store photo's, is it better to store the phots in a separate table or just add a column to the people table? I'm think about 60% will have photo's.

What is the best way to add a photo to a table?

Hi Graham,

The best practice is to create the image column in a seperate table and have a link column in the primary table.However the design is based on the retrieval of the table data,If your people data is always retrieved with image and no where else it is used in join with other tables it can be part of your main table itself

Regards,

Samsudeen B

|||

First why you want to store those Photos in Database. Consider to strore those on file system and store the File path on your table. It is very cheap to store on the filesystem(memory, retrival, storage & manipulations)..

If you want to store the Photos in database you need to store those in different table as Master Table. You can have those refrences on the detailed table. It is not a bad idea to keep the Image properties on the same (where the photos stored) table, like photo name, photo file ext, photo size etc..

|||Thank you, I had considered storing the filepaths (presumably nvarchar(260) is the best), but I thought the reason image type existed was because it was better to store images within the database, and I thought it might be more secure. But I can easliy fix security.|||

I know lot of people misunderstand with the name. Image is one of the Binary datatype and you can store any binary data like Word Docs, Excel and other binary files (image too). In Sql Server VarBinary(Max) is introduced.

|||

What's the advantage of storing other document types, rather than storing file paths?

Is it a security issue? Surely file permissons can fix that, or is it to do with replication, and distributed databases (not an issue for my needs)?