Database guru's help!

[Pirate]

Howdy,

My boss has given me a doosey of a job to build website where we can enter payment details and save them into a mysql database. He wanted it built into vbulletin but the amount of customers the company has it would really fuck up the system as there is about half a million of them.

so i need to have a system that allows me to enter this info, record the ID of the person who entered it and then have it all searchable and editable.

Does anyone know how to do this? I'm thinking of a pre-packaged system since this is beyond my web skillz.

the stuff that needs to be saved is the card number, account id, account name, paymetn date and payment amount.

I'm rooted.

[johny_roberts]

Worked at a company before that sells that stuff.

www.remedy.com

They have a system that they can build or you can build it yourself. I taught people how to build it so its not that rough. Just the cost.....

[Pirate]

that might be a bit too much. I have to keep the costs low since I don't have a budget :P

[johny_roberts]

Sorry other than that I have squat. It's out of my territory. Good luck mate......

[btwong]

Let me understand this:
1/ you want to make a seperate database to the vbulletin.
2/ a simple sort of form where a user/input person adds card number, amount, input persons id, etc and submits it, which adds it to the mysql database.
3/ Also a admin feature which allows searching of payment records via persons id, or whatever?

i would have say 2 tables.
1 table would be all the personal records, with the users details (just basic details)
the other would be the payment records.

To add payments, allow searching of the personal records, so when you find the person who the payment is from/for, you could click on a "add payment" button, which will allow you to enter the payment details, and then save them, with the personal id from table 1.

Also, in the personal details page (once you have searched and found the personal record you want to add the payment for) i would also have a sql script that goes through the payments table and grabs all the payments for that persons record, and displays them in the same page. Then if you want to edit the payment, just click on the payment number (or whatever) and you are then brought to the form with the details in it, so you can edit them and save them again.

I could explain this further, but i am not 100% sure what you mean!

edit: Yes i am a web developer, and work with MySQL, MS Enterprise, etc and a whole bunch of other web developing tools.

[MisterBishi]

Offtopic.com Statistics
Threads: 1,932,755, Posts: 39,586,795, Members: 124,317

VB seems pretty capable of those kind of numbers. Though you'd have to watch out for infighting, attention whoring and rabid anti-americanism amongst your customers.

[Pirate]

Let me understand this:
1/ you want to make a seperate database to the vbulletin.
2/ a simple sort of form where a user/input person adds card number, amount, input persons id, etc and submits it, which adds it to the mysql database.
3/ Also a admin feature which allows searching of payment records via persons id, or whatever?That's spot on.


i would have say 2 tables.
1 table would be all the personal records, with the users details (just basic details)
the other would be the payment records.

To add payments, allow searching of the personal records, so when you find the person who the payment is from/for, you could click on a "add payment" button, which will allow you to enter the payment details, and then save them, with the personal id from table 1.

Also, in the personal details page (once you have searched and found the personal record you want to add the payment for) i would also have a sql script that goes through the payments table and grabs all the payments for that persons record, and displays them in the same page. Then if you want to edit the payment, just click on the payment number (or whatever) and you are then brought to the form with the details in it, so you can edit them and save them again.You've pretty much got it. The boss wants it so the site can search by name, account ID, account name, card number and payment dates. You're thinking the right stuff, but I don't know how to code it since Im on my L's with this so far. I'm only just learning how to mess with databases.

[Pirate]

Oh I should also mention that it needs to log who entered the data

[btwong]

Thats not hard at all. If you would like, i can write you up a simple guide on how i would set it up (with tables and fields and some SQL) and how to use the tables and fields to cross-reference each other. This should point you in the write direction. Will probably take me about 30 mins to write it up. But i can't do it right now (bloody thing called work!), but i will do it tonight and then PM it to you.

Can i ask what language you will be writing it in? PHP, ASP, ASP.Net, CGI? or something else. (It doesn't really matter what language, cause the principal is all the same)

[btwong]

Oh I should also mention that it needs to log who entered the data

how will it know who has entered the data? IP address, or will they have to log in to get to it?

( i will even through in a little extra that can record when the data was first entered and by who, as well as when it was edited last, and by who!)

[Pirate]

thanks dude, I appreciate it.. the tracking of who enters the data was going to be done by the vbulleting system but I don't think thats going to work. So I have to sort something else.

I'm using php to make the site.

[evil]

You will need to store the credit card number in an encrypted format so that if a hacker breaks in they will need keys to decrypt it.

[btwong]

can u answer me this please...
how can you have a site like Zgeek, which would be very SQL/programming/form intensive, but no know that much about web programming. I am no paying you out, it just sort of bewilders me.

I know it was probably something previously setup like vbulletin, but still, you would have to know you why around a database.


also, for now, i will just store the person who is entering the payments/details IP address. Least then you can pin point which computer did it. Then once you have worked out how you are going to distingiush users, we can add a field to the database, and change a couple of lines in the code to record this also.

Anyway, expect a PM sometime tonight.

[Pirate]

You will need to store the credit card number in an encrypted format so that if a hacker breaks in they will need keys to decrypt it.Its not credit card info so it doesn't need encryption, it s payment card that the company makes and uses... basically a bpay type code thing on a card.

[Pirate]

can u answer me this please...
how can you have a site like Zgeek, which would be very SQL/programming/form intensive, but no know that much about web programming. I am no paying you out, it just sort of bewilders me.

Thats cool dude, the site is built on vbulletin which pretty much looks after itself. The rest of it are just hacks released by the vbulletin community (vbindex, 4images intergration and a few others). My web skillz are in design, production, HTML, DHTML, CSS and lame stuff like that. At the moment and can fudge my way around a mysql database and I know the very basics so I can fix stuff and get it to do what I want. I just can't code from scratch.

My skillz are getting better and there is going to be a new zgeek launched soon to show my stuff, but im more of a front end coder instead of the back end. Not 1337 enough for that yet.

[Mr Bigglesworth]

i would have say 2 tables.
1 table would be all the personal records, with the users details (just basic details)
the other would be the payment records.


And index tables roughly corresponding to your main search functions. Usually the index will consist of the keys plus as little pertinent information from the other tables as possible.

My knowledge is in MySQL and Coldfusion, and its getting pretty rusty.

[btwong]

And index tables roughly corresponding to your main search functions. Usually the index will consist of the keys plus as little pertinent information from the other tables as possible.

My knowledge is in MySQL and Coldfusion, and its getting pretty rusty.

that is correct...

the way i understand it will be a one to many sort of thing, as one user record, but multiple payments... so i am looking at 2 tables...

its a relatively straight forward setup...

i just have to dust off my PHP skillz!

[druid]

thanks dude, I appreciate it.. the tracking of who enters the data was going to be done by the vbulleting system but I don't think thats going to work. So I have to sort something else.

I'm using php to make the site.

You could share the login with vBulletin. Think of it as a sort of Single Sign-On. To log who did what you'll need a history table, typically called an audit trail. At this point designing gets complex - yes it seems simple but to prepare for future changes and minimize redundancy get someone to review* the db structure and then maybe dumb it down for your managers to see if it's got everything they want. And that's before sending any SQL commands to the db.

Also, there's half a million customers but how many people are actually going to use the db simultaneously? MySQL should be able to handle the records - the customers don't need to be vB users or do they?

*With no disrespect to btwong (who is going to great lengths) or anyone else but you're doing a business critical system so be very careful about stuff you get from the net.

[btwong]

You could share the login with vBulletin. Think of it as a sort of Single Sign-On. To log who did what you'll need a history table, typically called an audit trail. At this point designing gets complex - yes it seems simple but to prepare for future changes and minimize redundancy get someone to review* the db structure and then maybe dumb it down for your managers to see if it's got everything they want. And that's before sending any SQL commands to the db.

Also, there's half a million customers but how many people are actually going to use the db simultaneously? MySQL should be able to handle the records - the customers don't need to be vB users or do they?

*With no disrespect to btwong (who is going to great lengths) or anyone else but you're doing a business critical system so be very careful about stuff you get from the net.

but it sounds like an internal thing (i might be wrong) and only to keep a sort of record on a intranet of what card number they are using (and the card number is not a credit card number, but something you would distribute a say e.g. maccas free coffee card, and to a quick record of, that everyone can use).

Pirate, feel free to correct me, if i am wrong.

Plus i wont be coding it, i will be just giving good pointers and example on how to setup the database tables, how to link payment records with the user records by the paymentid/userid so people will be able to search for a user or payment and update/add/delete the payment, and some SQL pointers. Its more the programming of it. Pirate knows his HTML, CSS etc.

[Mr Bigglesworth]

so i need to have a system that allows me to enter this info, record the ID of the person who entered it and then have it all searchable and editable.

the stuff that needs to be saved is the card number, account id, account name, paymetn date and payment amount.


Table tbl_user
user_id*
user_name
user_password
user_firstname
user_initial
user_lastname
user_email
user_sizeofwang

Table tbl_payment
payment_id*
payment_accountid*
payment_userid
payment_accountname
payment_cardnumber
payment_date
payment_time
payment_amount
payment_transactioncomplete

Then for every seach, you create an index table. For instance, if youre searching for all payments made on the 14/5/05, create an index table using the keys from the two tables, plus the "payment_date" field from tbl_payments. That will speed searching up.

I hope this helps.

[druid]

Then for every seach, you create an index table. For instance, if youre searching for all payments made on the 14/5/05, create an index table using the keys from the two tables, plus the "payment_date" field from tbl_payments. That will speed searching up.


This is not a good idea. You'll be creating some horrible redundancy. Just create an index* or two for the most used columns or their combination.


*I hope I'm wrong and you meant a real index instead of a new table.

[Mr Bigglesworth]

whichever works best Druid ... like I said im rusty ..... been out of IT for a few years now.

[plext]

First up, from things you have posted in the past, it seems to me that these people are looking for more than just this one function from this application.

My first thought would be to sit them down and have an in depth discussion as to what their aims are and what they hope to get from this. Don't even think about starting a design until you have full agreement as to what you are trying to achieve.

It appears to me that what they want in the long run is a fully functioned intranet/datastore which means that adding modules at a later stage is going to be a primary concern as far as your design goes. Keep it simple and functional at first, you get too clever too early on and you will open up a nightmare in extensibility.

What is equally evident is that with 500000 client records, the existing dataset is going to be on the large side. Data import and export functions are going to be a required part of your interface. Sure it can all done back end, but do you have the time for such tasks and are all your users likely to be DBA's? I suspect the answers to those are no, so provide a way for end users to import and export bulk data.

Get yourself a good text on DB design now. Even if an experienced hand does the initial db design, you can bet London to a brick that at some stage you are going to need to extend it as needs change and functionality is added. These things are never static works of art.

If you attack this correctly it will still drive you insane, but you will learn so much it's just not funny and probably will enjoy it at the same time. Go get 'em.

[btwong]

Table tbl_user
user_id*
user_name
user_password
user_firstname
user_initial
user_lastname
user_email
user_sizeofwang

Table tbl_payment
payment_id*
payment_accountid*
payment_userid
payment_accountname
payment_cardnumber
payment_date
payment_time
payment_amount
payment_transactioncomplete

Then for every seach, you create an index table. For instance, if youre searching for all payments made on the 14/5/05, create an index table using the keys from the two tables, plus the "payment_date" field from tbl_payments. That will speed searching up.

I hope this helps.

this what i am thinking, and then the getting all the payments for each user will be along the lines of:

Select * from tbl_payment where payment_accountid = [selected user_id, eg. 1] order by payment_date asc

[Pirate]

Well I've done it, you can see it

http://www.zgeek.com/test/deft_payments_view.php

Intergration with vb is going to be a easy hack since the operator field can be hidden and given the value of the vb username so that suits my purpose. Now if only I can get this working on the work servers (somehow the mysql system wont allow me to enter data, but I can create databases...)

[btwong]

so did you whip that up yourself?

nice work!

[Pirate]

nah sorry, my hasle to get lunch. I used a program called PHPMagic plus. It makes the database and php front end which I can then hack for what I need.