Database

Re: How to select persons who didn't pay their bill for two months

18-Jul-14 7:47

Won't that have to look up each national_number in payment separately? It always looks like it does. Dead | X|

I don't use EXIST.

You'll never get very far if all you do is follow instructions.

Richard Deeming18-Jul-14 7:52

Richard Deeming

18-Jul-14 7:52

No, the SQL optimizer is pretty good at generating a sensible query plan. Smile | :)

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: How to select persons who didn't pay they'r bill for two mounth

Eddy Vluggen18-Jul-14 6:49

Re: How to select persons who didn't pay they'r bill for two mounth

18-Jul-14 6:49

So..

I would never show up in your list if I paid a dollar each month? Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Mycroft Holmes18-Jul-14 14:49

Mycroft Holmes

18-Jul-14 14:49

Eddy Vluggen wrote:
Bastard Programmer from Hell

Oh so appropriate.

Should be renamed The Edge Case from Hell

Never underestimate the power of human stupidity
RAH

Re: How to select persons who didn't pay they'r bill for two mounth

PIEBALDconsult18-Jul-14 7:42

how to select numbers (integers) only?

18-Jul-14 7:42

SELECT person.*
FROM person
LEFT OUTER JOIN payment
ON person.national_number = payment.national_number
AND payment_date > 'two month ago from now'
WHERE payment_date IS NULL

You'll never get very far if all you do is follow instructions.

Jassim Rahma16-Jul-14 22:52

Jassim Rahma

16-Jul-14 22:52

Hi,

I have employee_number field which has employee_numbers like this:

12
434
ABC345
QWR-567

I want to know how can I just select the numeric values to perform a MAX on it?

so ABC345 and QWR-567 will not be considered in the SELECT

Technology News @ www.JassimRahma.com

Richard MacCutchan16-Jul-14 22:54

Richard MacCutchan

16-Jul-14 22:54

You could always try a regex to parse out only the numbers.

thatraja16-Jul-14 23:01

thatraja

16-Jul-14 23:01

Googled? Check this thread[^]

thatraja

Code converters | Education Needed | Improve Everything^New

Mycroft Holmes17-Jul-14 12:49

Mycroft Holmes

17-Jul-14 12:49

Some guidance on the database you are using would be useful.

Take a look at the ISNUMERIC keyword in BOL.

Never underestimate the power of human stupidity
RAH

PIEBALDconsult17-Jul-14 13:52

17-Jul-14 13:52

WHERE id NOT LIKE '%[a-z]%' will eliminate any with alphabetic characters, that's a start, work from there.

You'll never get very far if all you do is follow instructions.

Jassim Rahma19-Jul-14 22:17

Jassim Rahma

19-Jul-14 22:17

I tried this:

SQL

SELECT employee_number FROM employees WHERE employee_number REGEXP ('[0-9]');

it's working excellent and I getting these numbers

7, 8, 9, 11, 1.222, 12, 13, 9, 15, 9, 9, 9

BUT....

when I run:

SQL

SELECT MAX(employee_number) FROM employees WHERE employee_number REGEXP ('[0-9]');

I get 9 where i should get 15.

why is that?

Technology News @ www.JassimRahma.com

PIEBALDconsult20-Jul-14 4:50

Handle to chat room with mysql database

20-Jul-14 4:50

Because they're strings; you'll need to convert to numeric fo rthe comparisons to work the way you want.

You'll never get very far if all you do is follow instructions.

Member 1094998616-Jul-14 4:08

16-Jul-14 4:08

I have been searching all day to find a better answer to my question but those I found was useful
but do not critically analyse my question.

I am building a chat room using php, mysql, jquery and ajax.
The target group members are 3000 people who will frequently
chat every day, so I am expecting like one million messages a day.
The chat room is open to all the members, that means
every member has the same priviledge to send and view
sent messages.

In this case, every member has the permission to delete messages whether sent by him or different members,
however deleted messages should not affect other users. I wouldnt also keep messages for more than two days,
meaning every sent message should be deleted after two days.

Below are the sample tables that represents the logics above

users table

| u_id |
| name |

messages table

| msgID |
| msgBODY |
| msgTime |
| senderID--FK |
| deleted_by_sender|

recipient table

| recID |
| msgID--FK |
| recipientID ---FK |
| deleted_by_recipient|

Now, if I am to implement the schema above, it means that every single sent message has to do 3000 inserts into the
recipient table and one insert into the messages table. This also means that if there are 50 sent messages within 1 minute there would be more
inserts within the one minute. At the same time 3000 people are viewing the messages. Awwsh! more work load on the database within that minute.
hmm!

Please is there a way to handle this, scalability and performance wise?

Any idea is appreciated. Thanks.

Eddy Vluggen16-Jul-14 5:02

16-Jul-14 5:02

Delete the recipient-table, and add a table like below;

DeletedMessagesPerUser
UserId
MessageId

Add a record to that table when the user "deletes" a message. When fetching all messages for a particular user, fetch all messages where the messageId isn't named in the DeletedMessagesPerUser table.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Member 1094998616-Jul-14 7:18

16-Jul-14 7:18

Thats a good idea. Please if i want to track unread messages per a user, it means i would have to create another table for the unread messages,right? or there is a better way to deal with it

Eddy Vluggen16-Jul-14 7:46

16-Jul-14 7:46

Member 10949986 wrote:
it means i would have to create another table for the unread messages,right?

Yup - a table with both a reference to the user, and a reference to the messages. Any message-id that's not in the table for that user, would be considered 'unread'.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Member 1094998616-Jul-14 7:53

16-Jul-14 7:53

Don't you think this would give my queries more load using four tables and doing many JOINs. Cant we have a simple way to solve this, which will increase speed. That even means, at every insert into the message table, there should be the same insert into the unread table, when all members are online

Eddy Vluggen16-Jul-14 8:05

16-Jul-14 8:05

Databases are optimized to do that stuff Smile | :)

Look into partitioning your table if you need more speed. If that's not enough, then you'd more be thinking about client-side processing. Do you need to "know" on the server whether a user read one of the messages?

If not, then you might be helped quickly with a XMPP-server. The local client could then keep track of the messages it receives, and which ones are read.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Member 1094998616-Jul-14 8:09

16-Jul-14 8:09

Eddy Vluggen wrote:
Do you need to "know" on the server whether a user read one of the messages?

Yes please.

Eddy Vluggen16-Jul-14 8:24

16-Jul-14 8:24

In that case you can't process that information on the client, and the server will have to.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

data modeling guy16-Jul-14 17:42

data modeling guy

16-Jul-14 17:42

Delete the recipient-table, and add a table like below;

MessageStatusPerUser
UserId
MessageId
IsDeleted

Add a record to that table when the user read a message set 'IsDeleted' to false.
When user delete a message, set IsDeleted to true.
When fetching all messages for a particular user, fetch all messages where the messageId isn't named in the MessageStatusPerUser table and Messages marked 'IsDeleted' as false

in summary:
messages not appearing in MessageStatusPerUser table are unread.
messages appearing in MessageStatusPerUser table and flagged IsDeleted as false are read.
messages appearing in MessageStatusPerUser table and flagged IsDeleted as true are deleted messages.

Member 1094998616-Jul-14 21:28

16-Jul-14 21:28

Alright, thanks soo much...If I understood it well, this means that a message is inserted into the MessageStatusPerUser only when the user has come online to read the message.That is it, right?

data modeling guy16-Jul-14 23:52

data modeling guy

16-Jul-14 23:52

yes, only when you consider the message is read by user.

Member 1094998617-Jul-14 1:45