RDMS Design Help

[Pallidrone]

I have a little issue that I am trying to fix here. We have a helpdesk with a SQL backend. The wonderfully people that set this thing up decided to use SQL, but not only did not normalize the database, they have absolutely no primary or foreign keys established.

What I am trying to do is normalize the database and set it up better, so that way I can program a Java frontend for it.

The current call log table has the following fields

Problem - LONGVARCHAR
WorkInProgress - LONGVARCHAR
Resolution - LONGVARCHAR

Each one of those fields represent a fair amount of input based on the call. I am thinking that part of the slow down in retrieving records is because of all of this information being placed on 1 row. Plus there is very little searching that can be done in a timely matter on these fields.

I want to separate them out to another table. What was suggested to me was to make a completely separate table that will have all this information in there and the link the Call log table up to it. I was thinking about having 3 separate tables, each one based off of these fields and then linking them up to the table.

Which method do you guys think is the best way to go? A single table that will be updated based off the information or 3 separate tables for this information?

[BuddhaMan]

When you're going to do your searching in the java app, will it search all 3 fields for the search term or just one specific field? Reason I ask is because I'd think if you're searching all three it would be more efficient to have all three in the same table as opposed to having 3 tables all loaded into memory with an INNER JOIN. You'd also only need 4 fields total (key + 3 data fields) and not 6 (3 tables with key + 1 data field) so that would help normalize the db and lower memory use (I'm guessing).

On a different note, wouldn't be faster to pre-hash the fields so the lookup would go a lot faster? You could setup a job to do it every night. That's what Google does. You type in a search term and it knows what articles have that keyword and brings those up. It doesn't search the fields on the fly. I think you know all this already so I'll stop there but can explain the idea more if requested.

Hashtable/hashmap ideas:

http://www.jguru.com/faq/view.jsp?EID=430247

http://www.pakzilla.com/2009/08/24/h...le-vs-hashset/


Disclaimer: I've only done SQL as a hobby, not professionally and I had to use the Microsoft Jet SQL engine in Access...which is shit.

[Pallidrone]

I would imagine that all 3 fields would be part of a query. On the screen for that specific call, it would reference all three pieces of information, and if one if not 'present' it would be blank.

You are probably right about the single table, which is why it was suggested to me.

SQL is fairly new to me. While I can write query statements, the actually setup and usage is still a foreign concept. Most of my work deals with flat files. I will have to look at that hashtable. If it is what I think it is, then it will be perfect for the next step in this, which is having 'keywords' in the helpdesk show as link to an internal web page that describes the problem/solution in detail.

[BuddhaMan]

There ya go man. Hash the table and spit out keywords with problem/solution IDs associated with each (the primary key in the table where problem text is located). On the webpage you mentioned you'd dynamically build it just like Google's. Show the first xx words of the problem and the first xx words of the solution then a "click here for more" hyperlink. The hyperlink would be of the form mysite.com/problem.jsp#id=xx where xx is what you looked up in the hashtable.

sKeyword | csvLinks
BSOD | 44,789,17
turn on | 67,23,56


Example keyword webpage (displayed after searching) for "BSOD"


First 50 words of issue #44
First 50 words of solution #44
Hyperlink to issue #44 for more reading

First 50 words of issue #789
First 50 words of solution #789
Hyperlink to issue #789 for more reading

Something like that. I'd actually have a title for each one and make that the link to the full amount of info...just like Google.

[Pallidrone]

Ahh - that is a fantastic idea Buddha. I am thinking that I will build an original table of keywords and then allow it to grow dynamically when the system is installed.

On a non related to this project, but related to SQL question ;

I kind of know how replication works, but could I have one computer with a specific set of data, another computer with it's own specific set of data and then a main computer that will have the combination of data from computer 1 and 2?

In other words, lets say I have a network of 3 computers. Since I am in a shitty building, I am using a wireless connection instead of a hard line. Sometimes that connection goes down, so instead of connecting to the server all the time, I will have a local database running on each machine. When I am normally 'connected' to the server, computer 1 would update the Widget table with information that is specific to computer 1, and computer 2 would update the Widget table with information specific to computer 2.

Now that I am running separate databases on each computer, I want the server to replicate that information so it now has information from both computer 1 and computer 2 in the widget table. Is that possible, or will there have to be a program that runs to update the table accordingly?

Hopefully I didnt confuse anyone, because I think I may have confused myself.

[BuddhaMan]

Sounds like you have computer A and B with server C. When able (the link is up), computer A & B do a bi-directional sync to server C. Man that REALLY sucks you have to work around the networking issue. I'd think one of the advantages of doing this whole new system would be to have just one database to do queries against, one place to maintain, etc. I guess you're just getting a system in place that's ready to be deployed into multiple locations around the world and there's no single point of failure.

What database server are you going to go with? MySQL, Postgres, Firebird, or something else?

Since you're in the planning stage, I'd start thinking of a way to store your db in the cloud online as the backup to the local backup. You got you grandfather, father, son thing going locally and then an offsite backup, and then your worst case scenario backup...the one in the cloud. One idea could be to export the db into a flat file, 7-zip it (LZMA compression), then encrypt it with PGP (or whatever) or use 7-zip's built-in AES256 encryption. Use SSH to copy it offsite or use something like Carbonite, Dropbox, etc.

http://en.wikipedia.org/wiki/List_of...ackup_services

Will the db server be running Linux? Sounds like you're on a budget so I assume the db is local on each machine and that third machine isn't a server, it's just another windows box in another room.

[Pallidrone]

I was juggling between MySQL and Postgres, but after playing around with both of them, I kind of like Postgres the best.

The question I was posing really is surrounding a separate project that I will be starting after this one. It is a POS system and networking is ALWAYS an issue with these people. So like you would have register 1 recording sales for itself and register 2 doing the same. If the server went down, you would like the computers to continue to process sales flawlessly until the server came back up online. I was thinking that ideally the register would run off of their own database, so that way if the server does die, then it will never effect the registers performance. However that sales thing is sticky point because I dont think that replication will work very well with it.

Then you have to add the layer of having multiple stores.....

It is funny you brought up the online cloud. I have a plan laid out for actually designing and creating a complete POS system on a cloud @ a cheap price for small companies that cant afford a big system or the little guy that travels a lot and has access to the web.

[BuddhaMan]

If you can pull it off that sounds like a cool idea! (the only POS experience I have is with Piece Of Shit systems, not Point Of Sale so I have no clue what woule be involved with that endeavor )

I was thinking about my example for the hastable and thought of possibly a better way. Rather than having one entry per keyword and all the keys in another field, have one key per entry and repeat the keyword.

Rather than:

sKeyword | csvLinks
--------------------
BSOD     | 44,789,17
turn on  | 67,23,56

Do this:

sKeyword | Links
--------------------
BSOD     | 44
BSOD     | 789
BSOD     | 17
turn on  | 67
turn on  | 23
turn on  | 56

That way when you do the SELECT FROM (problem,keyword) WHERE sKeyword = "BSOD" using the value of the Links field do an inner join to the problem table. You would end up with a table with all the info you need to build the webpage rather than running an additional query to go get that info. Follow?

This would also make the keyword table easier to maintain (add/delete entries when the words in the problem/solution table change). You could add/delete a whole record instead of editing the values in a field. Who knows, if you go the "rebuild keyword table nightly" route, you might just drop the old keyword table and replace it with a new one. That might be easier then adding/deleting individual records.

[=DBA=Ronin]

On a non related to this project, but related to SQL question ;

I kind of know how replication works, but could I have one computer with a specific set of data, another computer with it's own specific set of data and then a main computer that will have the combination of data from computer 1 and 2?

In other words, lets say I have a network of 3 computers. Since I am in a shitty building, I am using a wireless connection instead of a hard line. Sometimes that connection goes down, so instead of connecting to the server all the time, I will have a local database running on each machine. When I am normally 'connected' to the server, computer 1 would update the Widget table with information that is specific to computer 1, and computer 2 would update the Widget table with information specific to computer 2.

Now that I am running separate databases on each computer, I want the server to replicate that information so it now has information from both computer 1 and computer 2 in the widget table. Is that possible, or will there have to be a program that runs to update the table accordingly?

Hopefully I didnt confuse anyone, because I think I may have confused myself.

You should be able to configure something like that. You would probablly want to look into the Merge replication if computers 1 and 2 will be doing anything more than reading data. If they are changing data in any way (i.e. updateing, inserting, deleting, etc), you need the merge so that it can update the central repository once the network is back online. (I've never actually worked with Merge replication, I just know of it and only slightly at best)

However, if 1 and 2 are just meant to read the data that is comming from the central, you can just use distributed replication. Central would be setup as a publisher and 1 and 2 would subscribe to the publication in either a push or pull configuration.

It almost sounds as though you need to use both of these. Merge to update the server from each node, and then a distributor that would update each node with all the other nodes' data.

We use both of these concepts on our platform for different reasons. We have badge readers that we use Merge with as the readers are obtaining data from user input that needs to be written to the main database. In the event of a network outage, the readers can still accept swipes and it just stores them on its local db until the network is back up, at which point it sends the data back to the main.

For our web portal, we use a distributor on the main database that sends "cache" data to each IIS server via a local subsciption. The web portals then read from their local subscribtion databases where applicable to enhance performance. We used to do this via a .NET cache subsystem that would build a local data set into memory upon startup. This was extremely time consuming and the portal would be unusable until the caching was complete, which would take anywhere from 15 minutes to an hour depending on how big the client is. With using SQL replication, we can have a complete system up and running in about two minutes now, regardless of how big it is.

I am still relatively new to replication. I only recently started having to know it because some of our process based reports were programmed to use our old cache subsytem and when client's upgrade to our newer releases that use replication, not all the same data is being stored so we have to reprogram the report processes where needed. In order to test all of it, we have to now setup our internal systems using the replication, so I have been getting a dirty crash course in it.

[Pallidrone]

Buddha : I like that. I think I will have to add that later on in the program and see what I can do with it. Part of my thought process was to also link off to an internal 'wiki' like page that gives instructions on certain problems. So like say someone says that their network is down - the call would be flagged as having a wiki type page that the end user could then click the link to to bring up the hits for those keywords. On the page would be different steps that someone could take to fix a network being down.

Since it is a wiki page it would be dynamically updated as end users figure out how to fix different problems and either create a new entry or add on to the existing page with new solutions. I was also thinking about taking all the little utilities that I have written over the years and link them off as well. So that way if there is like corruption in the database (I am using flat files which seem to get corrupted all the time) or if someone needs to reload the data based off of another data set, it could link them right to the utility for them to use it.

Ronin : That is EXACTLY what I am looking for. I will have to read up more on it.


As far as the cloud POS, I have been thinking about this for the last year or so. My problem is that I do not know enough about SQL to create something like that. That is why I am starting on rewriting this help desk of ours. I figure that if I can do that and learn enough about SQL and Java in the process, I would be able to create a cloud POS. I truly believe that it would be something that people, especially smaller stores or companies would embrace.

Think about it as a computer consultant. You could go to a site and figure out what the store/company needs. While you are there you could input information into your POS through an iPhone or Droid app and right at that moment email out a proposal to someone. You could as a plumber or even a mobile pet groomer, do exactly the same thing and have all your information already stored no matter where you go.

Even a small little store like a bike or dollar store. You could setup a netbook with a basic UPC scanner and printer and run sales with a minimal amount of money invested into it.

I truly think that there could be a market for such a product.

[BuddhaMan]

Palli: take a look at what this guy did integrating Paypal with USBswiper and other web syuff to allow flea market & farmer's market vendors (and the like) to do credit card processing via Paypall as the backend.

http://www.angelleye.com/blog/paypal-integration/

https://www.usbswiper.com/

I dunno if that's what your trying to create. BTW: the only reason I know about this is because I favorited one of his music videos on Youtube and there were links in the video at one point and I went down that rabbit hole...

[Pallidrone]

Buddha : Close to that. I would like at providing some CC processing, but it would be more in the lines of something similar to this -

http://www.ordertopia.com/

but not as gay.

[BuddhaMan]

Got it...easy of the "gay". Your will be more business-like. Like what 37 Ladders or whatever that company is that put pro project management on the web.

[Admin]

I thnk that DROP ; will take care of your problems..

[Rico_Suave]

I thnk that DROP ; will take care of your problems..

Hey Palli -- what do you use with Java to connect to the DB? Anything like Spring or Hibernate?