1 Topic by RNilsson (edited by RNilsson 2003-12-03 02:56)

  • From: Göteborg
  • Registered: 2003-02-25
  • Posts: 168

Topic: Multitable Q

I've got these tables with fields listed below:

users
====
id
username
password
email
alias
sessionid
sessionexpire

bank access
=========
id
user_id
bank_implant_id
bank_cluster_id


implant bank
=========
id
bankname
implant_id
implant_ql
reserved_by


cluster bank
=========
id
bankname
cluster_id
cluster_ql
reserved_by

The table named 'bank access' regulates which respective bank id's.
When i log in to the site with my user, i want to check the bank access table so i know which bank's i'm allowed to view (i'm gonna make one drop-list for the implant bank, and another for the cluster bank).

How would such a SQL look like?
I'm learning, slow but steady about more complex db-operations, but please explain it so a 5-yr old could understand smile

Thanks

EDIT: Or is it perhaps better to make one acces-table for the implant-bank and one for the cluster-bank?
The more i think abour it it makes more logic to have separate access-tables but should work with only one too if i store the bank-id's in a |-separated list that i use as an array or something...

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Multitable Q

Hmm, I'm not sure I understand what you mean. Could you perhaps try to explain in other terms what it is you want to do? I get the impression that the query you want is a simple "SELECT * FROM `bank access` WHERE user_id=1337", but I'm guessing it's not that easy.

Also, what is an implant bank and what is a cluster bank? Can a user have access to more than one implant/cluster bank?

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

  • From: Göteborg
  • Registered: 2003-02-25
  • Posts: 168

Re: Multitable Q

Implants and Clusters are special items that enhances your characters attributes, skills and abilities in the game Anarchy Online.

Now to the question, i'll try to explain some more.

For every useraccount (my friends) that i add via the admin-interface, i also add access to the correct banks, (We have separate banks for the different character-pairs depending on which combo we play etc).

These are all example values.
I have the following Implant Banks:
Engi/Meta Implants
Fixer/Advy Implants
Keeper Implants
Private Implants

I have the following Cluster Banks:
Engi/Meta Clusters
Fixer/Advy Clusters
Keeper Clusters
Private Clusters

I want to be able to control access (which banks that show up in a list) for each user that logs in.

I think it'd be easier to have one access-table per bank-type like this:
ImplantAccess[id|user_id|bank_id]
ClusterAccess[id|user_id|bank_id]

And if i want to list user_id 1337's implants and cluster banks (he has access to keeper and private) i might want to do something like this:
select * from implant_bank as ib, cluster_bank as cb where user id = 1337
If i'm right, i'm getting all implant-banks and all cluster-banks with user_id 1337, right?

I'm using PGSQL if it make any difference, and i might use some cool pg-features later but right now i'm learning plain SQL so...

  • From: 127.0.0.1
  • Registered: 2001-11-02
  • Posts: 9,167

Re: Multitable Q

What I would probably do is to not have separate tables for implants and clusters. If you look the the table structure of the two tables, they are almost identical. That usually means you've made a design error. Use one table for both implants and clusters and add a flag column to tell whether it is an implant or a cluster. You can then do something like this:

SELECT b.bankname, b.type FROM `bank` AS b INNER JOIN `bank access` AS ba ON ba.bank_id=b.id WHERE ba.user_id=1337 ORDER BY b.type

This will return all banks that the user with ID 1337 has access to. The results will be ordered by type so you can easily create drop down lists.

Edit: Well, at least I think it will. I won't guarantee anything :)

"Programming is like sex: one mistake and you have to support it for the rest of your life."

Rickard's Website

  • From: Göteborg
  • Registered: 2003-02-25
  • Posts: 168

Re: Multitable Q

Yeah, that is something i did have at first, but didn't know how to make the sql-statements correctly.
But now i will make only one bank-table with types instead.
More Q's will no doubt surface during development.

Thanks