Jump to content

Another database structure


phppup
Go to solution Solved by Barand,

Recommended Posts

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Solution

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    |                |
+------------+---------------+------+-----+---------+----------------+

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.