-1

Total newb here trying to create a database and figure out the layout of my tables etc.

I want to do something that seems to be relatively simple. I want to create a database that will keep track of dice rolls for the game of craps. But I want it to be capable of keeping track of many different users. I started creating a "rolls" table and a "customers" table. Under rolls, I want to track both dice (left and right) and the corresponding number (1 through 6) for each dice. I have the roll number, which will autoincrement, date, left and right for the columns.

Question I have is, do I simply mix in ALL rolls of every potential user into this one table by also adding in customer-id from my customer table? Or is this a really bad idea?

I am anticipating 50+ users who will each record thousands of rolls over time. I will need to perform regular queries of this database to retrieve records for each individual user and deliver statistics to them. Wondering if constantly querying this one table will cause issues and/or if anyone has suggestions on a better way to approach this. Thanks.

Tyler
  • 1

1 Answers1

0

Online example fiddle.

Here is an example rolls table:

CREATE TABLE `rolls` (
  `roll_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int unsigned NOT NULL,
  `leftroll` tinyint unsigned NOT NULL CHECK (`leftroll` <= 6),
  `rightroll` tinyint unsigned NOT NULL CHECK (`rightroll` <= 6),
  PRIMARY KEY (`roll_id`),
  KEY `i_customer` (`customer_id`)
) ENGINE=InnoDB

I've used leftroll and rightroll as left and right are keywords.

Not sure what you mean by mixing however this is the collection of data you need. Don't worry about constant use or inserts. Databases are designed for that.

danblack
  • 12,130
  • 2
  • 22
  • 41