Questions about tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
chrchcol
Forum Newbie
Posts: 15
Joined: Fri Jul 14, 2006 5:35 am

Questions about tables

Post by chrchcol »

I am working on a ftp user control panel. I basically have a adduser form that will, add a username, password, dbquota, and some other options.

I am thinking that would be one table.


1st question: Do I need to make the username the primary key so that when you search for users, it will bring up only that users specific info?


Once a user is added that user will also have subaccounts and passwords, virtual ftp accounts and passwords, and virtual ftp directories.

This would be another table?

How do I add the username to both tables?

Also when querying each users page, and bringing up there data, can I list the data from both tables?

Chris
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

These are really only questions you can answer because they define the behaviour of your application, which only you know.

Draw yourself an ERD (entity relationship diagram). The current understanding of ERDs is an UML inspired, complicated thing but the oldskool version I learnt is really simple which is what I'm advicing here.

Simply put you have entities (boxes) that are connected by lines (relationships) the arrows are used to indicate 'many'. You have three different types of relationship.
  • One to one (E-----E)
  • One to many (E>----E)
  • Many to many (E>---<E)
Image
An example of these used:
Image
In this diagram you can tell that:
  • A publisher has many books
  • A book has many reservations
  • A book has many copies
  • A copy has many loans
  • A loan has many borrowers
  • .....
An example of a many to many relationship might be between a customer and a supplier. A supplier supplies to many customers and a customer uses many suppliers. An example of a one to one relationship might be a person to a liver, each person has a liver and each liver exists inside a person (funny example i know).

This is all you need to do to work out how your tables will be related.

There is a process called normalization which you can use to work out when you need separate tables and when you don't. Finding a good article on this is really difficult but I managed to find this. It can take a while to get your head around it. The rule with database normalization is, learn it, implement it until you are comfortable with it (maybe a number of separate projects), once you are comfortable start to break the rules a bit as you get more experienced you'll know where to do so to make your life easier.

Hope this helps.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

To answer your questions as easily as possible...

Yes, you can pull data from two (or more) tables using simple SQL joins. I would not store the username as the primary key, but instead I would use a user_id and then use the id to join, select, edit etc. You can store the permissions in the user table, or in another table, depending on the structure of their permissions. If they are simple and can be added to the user row, put them in the user table. Otherwise create a permissions (or similar) table and join the user table to it during selecting.
Post Reply