Print Page - MySQL table pointers

Title: MySQL table pointers
Post by: genjix on April 22, 2011, 04:12:27 PM

Is there a way to have a field in MySQL that acts as a pointer to another table?

I want to have a bunch of different records with different fields but sharing some common ones.

common field A | common field B | pointer

then I could do:

SELECT mytbl ... JOIN POINTER(mytbl.pointer) ...;

Or do I need to have a VARCHAR(6) ident field and simply have a switch... case in my application that joins depending on which "other" table is needed?

Title: Re: MySQL table pointers
Post by: error on April 23, 2011, 02:35:32 AM

Sounds like something's terribly wrong with your table design. Can you provide some more details of what it is you're trying to accomplish?

Title: Re: MySQL table pointers
Post by: Joshi33xx on April 23, 2011, 10:22:03 AM

Look into creating a "view".

http://en.wikipedia.org/wiki/View_(database)

That might let you do what you need. The "default" is then whatever data you define it to be in the view.

You can have as many views as you like.

Title: Re: MySQL table pointers
Post by: genjix on April 23, 2011, 10:46:18 AM

For Britcoin there are 3 different types of withdrawal: Bitcoin, UK-domestic, UK-international:

Code:

mysql> describe requests;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| reqid     | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| req_type  | varchar(6)          | NO   |     | NULL              |                |
| uid       | int(10) unsigned    | NO   |     | NULL              |                |
| amount    | bigint(20) unsigned | NO   |     | NULL              |                |
| curr_type | varchar(6)          | NO   |     | NULL              |                |
| timest    | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| status    | varchar(6)          | NO   |     | VERIFY            |                |
+-----------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

mysql> describe bitcoin_requests;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| reqid | int(10) unsigned | NO   | PRI | NULL    |       |
| addy  | varchar(44)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe international_requests;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| reqid | int(10) unsigned | NO   | PRI | NULL    |       |
| iban  | varchar(36)      | NO   |     | NULL    |       |
| swift | varchar(12)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe uk_requests;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| reqid     | int(10) unsigned | NO   | PRI | NULL    |       |
| name      | varchar(40)      | NO   |     | NULL    |       |
| bank      | varchar(40)      | NO   |     | NULL    |       |
| acc_num   | varchar(8)       | NO   |     | NULL    |       |
| sort_code | varchar(6)       | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

Each of those requests 'shares' the requests tables but adds their own specific data.

I'd like some way to tell which data should be joined to the requests tables and what type of request it was (Bitcoin, UK-domestic, UK-international).

Maybe an accounts field or using views is the way to go.

Title: Re: MySQL table pointers
Post by: Alex Beckenham on April 23, 2011, 11:12:24 AM

Your schema looks fine; Probably how I would have set it up, but I'm not sure what you want to query from it.

Can you write in plain English what you want to retrieve from the db and I can translate it to sql for you?

Here's an example of grabbing some particular UK request:

Code:

SELECT * FROM requests,uk_requests WHERE (requests.reqid=uk_requests.reqid) AND (uk_requests.acc_num='3');

Title: Re: MySQL table pointers
Post by: genjix on April 23, 2011, 11:34:42 AM

Well your query does a cartesian join then filters out some rows using a WHERE and is less efficient than JOIN ... ON ... ;D

So how could I tell whether reqid='133' is from either Bitcoin, international_requests or uk_requests in a single query? I know I could do SELECT 1 FROM uk_requests WHERE reqid='133'; to see whether it exists in uk_requests, but how about for all 3 in one go?

Is an extra field in requests the way to go?

Title: Re: MySQL table pointers
Post by: Nefario on April 23, 2011, 02:06:28 PM

Quote from: genjix on April 23, 2011, 10:46:18 AM

For Britcoin there are 3 different types of withdrawal: Bitcoin, UK-domestic, UK-international:

Code:

mysql> describe requests;
+-----------+---------------------+------+-----+-------------------+----------------+
| Field     | Type                | Null | Key | Default           | Extra          |
+-----------+---------------------+------+-----+-------------------+----------------+
| reqid     | int(10) unsigned    | NO   | PRI | NULL              | auto_increment |
| req_type  | varchar(6)          | NO   |     | NULL              |                |
| uid       | int(10) unsigned    | NO   |     | NULL              |                |
| amount    | bigint(20) unsigned | NO   |     | NULL              |                |
| curr_type | varchar(6)          | NO   |     | NULL              |                |
| timest    | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
| status    | varchar(6)          | NO   |     | VERIFY            |                |
+-----------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)

mysql> describe bitcoin_requests;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| reqid | int(10) unsigned | NO   | PRI | NULL    |       |
| addy  | varchar(44)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> describe international_requests;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| reqid | int(10) unsigned | NO   | PRI | NULL    |       |
| iban  | varchar(36)      | NO   |     | NULL    |       |
| swift | varchar(12)      | NO   |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> describe uk_requests;
+-----------+------------------+------+-----+---------+-------+
| Field     | Type             | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| reqid     | int(10) unsigned | NO   | PRI | NULL    |       |
| name      | varchar(40)      | NO   |     | NULL    |       |
| bank      | varchar(40)      | NO   |     | NULL    |       |
| acc_num   | varchar(8)       | NO   |     | NULL    |       |
| sort_code | varchar(6)       | NO   |     | NULL    |       |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

.

Each of those requests 'shares' the requests tables but adds their own specific data.

I'd like some way to tell which data should be joined to the requests tables and what type of request it was (Bitcoin, UK-domestic, UK-international).

Maybe an accounts field or using views is the way to go.

In your three tables for bitcoin, brit, & international just add a field for the primary key of your fourth table that they are all trying to reference, say the table is called transactions, and the primary key is an auto increment integer called id.In the other tables add the field transaction_id as an integer. They should all be able to reference the common table now

Title: Re: MySQL table pointers
Post by: Alex Beckenham on April 23, 2011, 02:48:29 PM

Quote from: genjix on April 23, 2011, 11:34:42 AM

Well, I'm sure the following is probably non-working utter crap, but it might give you ideas none-the-less...

Code:

SELECT bitcoin_requests.reqid='133' AS is_bitcoin, international_requests.reqid='133' AS is_international, uk_requests.reqid='133' AS is_uk
FROM bitcoin_requests,international_requests,uk_requests
WHERE (bitcoin_requests.reqid='133')OR(international_requests.reqid='133')OR(uk_requests.reqid='133');

Sorry can't be any help.

Title: Re: MySQL table pointers
Post by: genjix on April 25, 2011, 12:51:21 AM

Thanks.

Bitcoin Forum

Other => Off-topic => Topic started by: genjix on April 22, 2011, 04:12:27 PM