Which DB to use? Oracle? SQLS? Other? [Was: a database design/implementation question

[Mal Reynolds]

I am in the initial stages of deciding on an implementation/design scheme for a database. I have looked for information using google, etc., and got some information, but not really enough. If there is someone who has experience with databases who has a few minutes to answer a few questions it would be greatly appreciated. First I will try to describe what this database is trying to accomplish:

- first of all, the user base will be small by today's standards. there will be probably no more than 50 people accessing this database. possibly more in the future, but not immediately.

- the actual data being stored is relatively small (mostly text/numerical values, possibly some small pictures).

- the users will not necessarily be trying to find certain data entries! this is important! it needs to be able to find specified entries, but more so, users will be concerned with the relationships between entries (i.e. "what other entries have similar/identical values for StartDate to this entry?").

- users will be accessing this database over a secure connection to a LAN.

I can write C++/Win32api software that will store this information, access, and search info, and link relationships between this data, and allow an administrator to set access rights, or define how the searches return information.

My Question: Considering that many of the users may be mostly computer illiterate, and will not understand how a complex application works, should I learn how to implement a standard type of database (by standard I mean an Access db, mySQL, etc.), or should I just write my own implementation and tailor it specifically to this need? so in other words, based on the specifications, will a standard db implementation work, or should i write one?

[TheCPUWizard]

1) Use a standard database. My recommendation would be SQLServer Express edition [free, and fairly extensible, plus 100% upwards compatible to full enterprise edition SQLServer. You can not possibly (unless you have a few man-decades, and millions of dollars) write something equivilant.

2) Study up on "Set Theory"...This is how RDBMS work. If you dont understand the concepts intuitivly, you are bound to run into problems.

3) Study up on "Data Normalization"...Make sure you are comfortable with the five primary forms of normalization.

[olivthill]

Usually, users don't write SQL queries. Instead they fill fields in a form and press a button. The forms is sent to a server where a SQL query is generated with the contents of the fields used as arguments. The server sends the results to the user in some fields of the form, or in a new form.

In a particular accounting software, there was a screen where the user could write SQL queries, but only of the Select type. The accountant, which had a lots of degrees and was far from stupid, did not like very much that screen. Every time he wanted a new query, he tried to write the SQL query by himself, and then he came to see me in the IT department in order to get some help.

I second TheCPUWizard about the importance of knowing "data normalization" also named as the rules of E. Codd.

[TheCPUWizard]

Usually, users don't write SQL queries. Instead they fill fields in a form and press a button. The forms is sent to a server where a SQL query is generated with the contents of the fields used as arguments. The server sends the results to the user in some fields of the form, or in a new form.

Of course there is a difference between what the user sees, and how the data is actually stored and manipulated. If there wasn't we could scrap all applications, and just deploy SQL Management Studio.

My post was refering to the internals of the program to be developed. The end user should not care (nor be exposed) to such details, of course.

In a particular accounting software, there was a screen where the user could write SQL queries, but only of the Select type. The accountant, which had a lots of degrees and was far from stupid, did not like very much that screen. Every time he wanted a new query, he tried to write the SQL query by himself, and then he came to see me in the IT department in order to get some help.

It is amazing you developers will often expose this type of functionallity very badly. In 1983 I wrote a program for Hearst Business Publishing that exposed a "query" definition in a manner that was well understood by the business user (and looked nothing like SQL). 25 years later that basic architecture is still part of my library.....

I second TheCPUWizard about the importance of knowing "data normalization" also named as the rules of E. Codd.

[Mal Reynolds]

thank you very much for your replies! I will learn how to use SQLServer, brush up on Set Theory, and research data normalization. I would assume that the easiest way to set up a UI would be to write a web-based interface using javascript and php, but if you have other suggestions I'd be more than willing to hear them! thank you again, take care.

[TheCPUWizard]

I would assume that the easiest way to set up a UI would be to write a web-based interface using javascript and php, but if you have other suggestions I'd be more than willing to hear them! thank you again, take care.

That is very subjective, depending on your target platform, and your experience.

IF I was doing it, and the target was Windows, I would strongly consider a .NET (C#) solution. For a non-Windows environment I would probably go with C++ [which I can write in my sleep, while PHP requires me to think, since I rarely use it].

[Mal Reynolds]

thank you, i will definitely consider using .NET, but out of curiosity ( since i am not as familiar with .NET ), what makes .NET a better solution than C++ for the windows environment?

[TheCPUWizard]

thank you, i will definitely consider using .NET, but out of curiosity ( since i am not as familiar with .NET ), what makes .NET a better solution than C++ for the windows environment?

Not "better" per se...different with a number of advantages.....

1) A richer framework (library).
2) A safer (less error prone) development environment.
3) A more flexible environment (you can easily mix languages in a single solution - C#,C++,VB,Fortran,Cobol,APL,Ruby,F#.....)

[cjard]

I'd use Oracle (which also has a free edition) rather than SQLServer; I find it far more flexible

[TheCPUWizard]

I'd use Oracle (which also has a free edition) rather than SQLServer; I find it far more flexible

Largely a subjective decision IMHO. We can difinately create a thread to discuss the features on each side (I think it would be a great idea - if we can get people with different view points involved....)

[cjard]

It's the little code-level things about SQLServer that I find most hindersome. If someone could show me how to format a datetime column into a string, pattern "yyyyMMddhhMMss", that would be great.. I dont find any of the predefined date formats appealing


Here's a start (from an Oracle site, aimed at ora developers creating a sqlserver based app)
http://www.psoug.org/reference/sqlserver.html

[TheCPUWizard]

It's the little code-level things about SQLServer that I find most hindersome. If someone could show me how to format a datetime column into a string, pattern "yyyyMMddhhMMss", that would be great.. I dont find any of the predefined date formats appealing


Here's a start (from an Oracle site, aimed at ora developers creating a sqlserver based app)
http://www.psoug.org/reference/sqlserver.html

1) Lets take this to another thread..or PM (Brad and friends have been encouraging threads to stay on original topic and not drift...)

2) Since you already posted the question...There are alot of choices. The MOST flexible would be to simply develop the formatter in C# and install it as a CLR UDF. The capabilities of this are extremely powerful.

[cjard]

2) Since you already posted the question...There are alot of choices. The MOST flexible would be to simply develop the formatter in C# and install it as a CLR UDF. The capabilities of this are extremely powerful.

Oh come on! We're formatting a date, not putting in a complex BL! SQLserver native way, no .NET hacks, no lengthy T-SQL procedures please! (Similarly, anything that Oracle cannot do that SQLServer can will not be proposed in java, PL/SQL.. )

[TheCPUWizard]

Oh come on! We're formatting a date, not putting in a complex BL! SQLserver native way, no .NET hacks, no lengthy T-SQL procedures please! (Similarly, anything that Oracle cannot do that SQLServer can will not be proposed in java, PL/SQL.. )

That [IMHO] is where a big difference comes in in philosophy (again a highly subjective topic).

I tend to look at the comprehensive solution. Eliminating the use of features (and CLR integration is definately NOT a "HACK"...) is simply a device for skewing the results.

Your proposal (to my mind) is the exact same as stating you cant use any command that has an "R" in it (Do you eat oysters? )...

I propose that a fair test would be to (conceptually) setup a client along with a number of DB servers (SQLServer 2008, Oracle, etc). All servers must expose the same functionallity, but one has complete freedom as to the server side of the implementation.

This mimics how most enterprise applications are setup with the two different layers often being designed and developed by independant teams and the ONLY communication between the teams is the API.

The other important thing, is to establish the metrics of what is "better". In business this translates directly to achieving the goal at the lowest possible cost [Total Lifetime].

I will respond directly to your question in my next post...

[TheCPUWizard]

Oh come on! We're formatting a date, not putting in a complex BL! SQLserver native way, no .NET hacks, no lengthy T-SQL procedures please! (Similarly, anything that Oracle cannot do that SQLServer can will not be proposed in java, PL/SQL.. )

My first question (should have been) WHY is a database doing ANY formatting of data????

This responsibility typically should fall to the application.