Christiaan Posted November 1, 2012 Share Posted November 1, 2012 Hello Everyone. I have a question. I am building a raffle database. The raffle-tickets will be awarded manually. Now, I have the following three tables: raf_raffles ------------------------------------ id int(5), primairy, AI Month varchar(10) Year Varchar(4) OpenDate Date CloseDate Date raf_players ------------------------------------ id int(5), primairy, AI Badge Varchar(5) Fname Varchar(32) Lname Varchar(32) raf_tickets ------------------------------------ id int(5), primairy, AI month Varchar(12) year Varchar(4) badge Varchar(5) tick_plus int(4) tick_minus int(4) I have a form that will insert the info into the different tables. The difficulty I am experiencing here is to make sure that the shown data matches. Person A has x number of tickets for the raffle in November. Person B has y number of tickets for the raffle in October. Can someone give me some pointers? I'm new to php so all help is greatly appreciated. Christiaan Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 1, 2012 Share Posted November 1, 2012 What are tick_plus and tick_minus about? And how are you linking the tickets to the actual raffle? Quote Link to comment Share on other sites More sharing options...
Barand Posted November 1, 2012 Share Posted November 1, 2012 ... and also "badge" which is stored in two tables? Quote Link to comment Share on other sites More sharing options...
Christiaan Posted November 1, 2012 Author Share Posted November 1, 2012 Tickets_plus is where any positive amount of tickets will be inserted into. Tickets_minus are tickets that will be deducted. Badge is the unique ID-number for each player. So I had put that in there to be able to reference two table. The idea of the raffle is to motivate people performing better. You do good: you get more tickets. You screw up: Tickets will be deducted. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 1, 2012 Share Posted November 1, 2012 Still not getting how tickets are associated to a raffle, and why not juat have a field that contains the number of tickets that said badge number has and add or subtract from it depending on performance? Quote Link to comment Share on other sites More sharing options...
Barand Posted November 1, 2012 Share Posted November 1, 2012 It's not the number (quantity) of tickets you need in a raffle. You need to know the actual unique ticket numbers/ids held by each person Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 1, 2012 Share Posted November 1, 2012 It's not the number (quantity) of tickets you need in a raffle. You need to know the actual unique ticket numbers/ids held by each person that's.....a damn good point!lol I never even looked at it from that angle, all that needs to be in the ticket table is unique id, user id, raffle id (although if you are recycling the raffle tickets for each raffle then obviously this isn't needed either) Quote Link to comment Share on other sites More sharing options...
Jessica Posted November 1, 2012 Share Posted November 1, 2012 Still not getting how tickets are associated to a raffle, and why not juat have a field that contains the number of tickets that said badge number has and add or subtract from it depending on performance? Have you ever entered a raffle? Quote Link to comment Share on other sites More sharing options...
Christiaan Posted November 1, 2012 Author Share Posted November 1, 2012 Thanks every one. First of all: There are no unique ticket numbers. So that is not a concern. The amount of 'tickets' will be put into a spreadsheet, where each row is a ticket. With the rand() function it will be mixed up. Here is what the intention is: An employee performs good. Get tickets awarded. The employee doesn't perform good: Tickets gets deducted. I want to do that in a database, so employees can see their number of tickets grow, real time. Can any one help with that? Quote Link to comment Share on other sites More sharing options...
Jessica Posted November 1, 2012 Share Posted November 1, 2012 Are you trying to keep record of when/why they got added/removed as well? Quote Link to comment Share on other sites More sharing options...
Christiaan Posted November 1, 2012 Author Share Posted November 1, 2012 Not in detail. We just need to know the month/ year, employee and nr of tickets. Thats about it. Quote Link to comment Share on other sites More sharing options...
Jessica Posted November 1, 2012 Share Posted November 1, 2012 I would have two tables. Employees employee_id employee_name (etc etc) tickets (unsigned int) Transactions transaction_id employee_id manager_id (also an employee, look into normalization if you need to) transaction_tickets (unsigned int) datetime (etc etc) Make sense? Quote Link to comment Share on other sites More sharing options...
Christiaan Posted November 1, 2012 Author Share Posted November 1, 2012 Yes, makes sense. With this set-up I'm still able to run a search on diffrent months. I'll just add one more column to the employee table: Line of business. Since I would like to be able to display total of tickets per LOB as well. Quote Link to comment Share on other sites More sharing options...
Jessica Posted November 1, 2012 Share Posted November 1, 2012 What sort of data is line of business? That may need to be it's own table, and then you store an ID in the employees table. Quote Link to comment Share on other sites More sharing options...
Christiaan Posted November 1, 2012 Author Share Posted November 1, 2012 (edited) We have 3 lines of business. Each LOB has a fixed group of employees associated. For example: LOB A: 15, LOB B: 90 LOB C: 35 LOB D: 2 So than that table would be: ID int(5), Primairy index, auto increment LOB Varchar(32) Edited November 1, 2012 by Christiaan Quote Link to comment Share on other sites More sharing options...
Christiaan Posted November 1, 2012 Author Share Posted November 1, 2012 I have set this up as tables now: raf_employees: -------------------- badge fname lname role tickets raf_lob --------- id lob raf_transactions --------------------- trans_id badge manager_badge trans_tickets date_time raf_roles ------------ id role This to make it easier to assign roles to employees. Quote Link to comment Share on other sites More sharing options...
Christian F. Posted November 2, 2012 Share Posted November 2, 2012 The line of business could quite easily be an ENUM, since you have a very limited and clearly defined subset of options. No need to create an entire table for just one value. Quote Link to comment Share on other sites More sharing options...
Christiaan Posted November 2, 2012 Author Share Posted November 2, 2012 Hmm, but that would go for roles as well, right? There are only like 4 roles. - Administrator - SuperUser - Manager - User Quote Link to comment Share on other sites More sharing options...
Christian F. Posted November 2, 2012 Share Posted November 2, 2012 Yep, indeed it would. Didn't see your last post before I posted. Quote Link to comment Share on other sites More sharing options...
Jessica Posted November 2, 2012 Share Posted November 2, 2012 I personally would keep them as their own tables. Quote Link to comment Share on other sites More sharing options...
Christiaan Posted November 2, 2012 Author Share Posted November 2, 2012 I kept them in two separate tables. Not that much extra. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.