Another database structure

[phppup]

I'm getting accustomed to spreading my data over multiple tables, and trying to do it right.

Assuming that several small tables i are more optimum than one big table (since there are less rows to touch - even if they are being ignored), is touching a table for a single column advisable? I know repetition is a no-no, but should oft needed data be stored together (even if unrelated)?

Example: On login there is a check for user and password (from USERS).
But then I want to make sure the account is active (it will expire after 30 days and days is stored with other timestamps) so check ACCOUNT activity. And that the user logged in within the last 5 days (check INFO) or used a PROMO table code etc, etc.

Each of these tables needs to be visited to compare data. Is this the correct/best approach? Or is there an alternative way to make 'regularly required info' more accessible?

[Barand]

36 minutes ago, phppup said:

Each of these tables needs to be visited to compare data.

It would still only be a single query (with a few joins). What is your problem with that?

[phppup]

No problem. I just wanted to confirm that hopscotching through the tables regularly was the right way to go (versus a single table with so the necessary data parked and available). Perhaps I'm just thinking too "humanly" for setting up an "automated" system.

PS: how do I extract the diagram of my db structure similar to what I've seen posted in this forum?

_____

|      |

-----     ? Thanks.

[phppup]

No problem. I just wanted to confirm that hopscotching through the tables regularly was the right way to go (versus a single table with so the necessary data parked and available). Perhaps I'm just thinking too "humanly" for setting up an "automated" system.

So it's the number of queries that actually cause extra load?

Is it preferable to gather all my data and then evaluate

sql = "SELECT X FROM TABLE.... JOIN... JOIN.... JOIN
if ( evaluate results to determine action...

 

Or cascade through IFs that essentially create extra query trips

$sql = "SELECT x from table1 WHERE 
if ( $x .... condition TRUE
 $sql2 = SELECT.. JOIN..
         another condition... 

but has the potential to reduce the bed to access resources.

Or does it not matter at all?

 

PS: how do I extract the diagram of my db structure similar to what I've seen posted in this forum?

_____

 

|      |

 

-----     ? Thanks.

[Barand]

1 hour ago, phppup said:

PS: how do I extract the diagram of my db structure similar to what I've seen posted in this forum?

I type them out in my editor then paste them as code to retain the monospaced font.

[phppup]

I'm guessing that yes, it's the number of queries that at to load, and retrieving extra days is still better than an extra query.

As for the diagrams, awesome of you.

I thought there was a sql clause like SHOW (which I have not gotten to work) that could be used.

[Barand]

These two are useful for showing us your table structures:

SHOW CREATE TABLE order_item;

CREATE TABLE `order_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

 

or, using the mysql CLI,

mysql> DESCRIBE order_item;

+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int(11)       | NO   | PRI | NULL    | auto_increment |
| order_id   | int(11)       | YES  |     | NULL    |                |
| product_id | int(11)       | YES  |     | NULL    |                |
| qty        | int(11)       | YES  |     | NULL    |                |
| price      | decimal(10,2) | YES  |     | NULL    |                |
+------------+---------------+------+-----+---------+----------------+