I'm creating a database for a photography website and I want it to allow three main things -

Allow the owner/admin to create client accounts and credentials,

Specifying which photos should go into three different portfolio galleries on the site, and,

Displaying a unique client's photos (and only their photos!) to them when they log in.

This is my first database design ever - based on responses below, I've added that emphasis ;) and edited the design 开发者_如何转开发as below.

IMAGES

image_id,

filename,

description,

client_id,

date_uploaded,

USERS/CLIENTS

client_id,

client_name

username,

password,

PORTFOLIO

portfolio_id,

portfolio_name,

PORTFOLIO_IMAGES

id,

image_id,

portfolio_id,

Am I correct in thinking that the final id in PORTFOLIO_IMAGES would allow me to display one image in multiple galleries?

Thanks

---

As it is your first DB-Design and as you may have mentioned in the comments here is something essential missing: ER-Diagram. This helps a lot understanding what's going on.

ER-Diagram

Synonyms: User=Account, Image=Photo, Gallery=Portfolio
Known Roles: "Admin", "Client"
Examples for Rights: "Create Account", "Delete Account", "Watch images", "Add Gallery", "Remove Gallery", "Upload image", "Delete image", ...

Table Design

User

• id
• name
• password

Image

• id
• user_id
• filename
• description
• upload_date

Image_Gallery

• image_id
• gallery_id

Gallery

• id
• name

---

User_Role

• user_id
• role_id

User_Right

• user_id
• right_id

Role

• id
• name

Role_Right

• role_id
• right_id

Right

• id
• name

You may want to remove all the things with Right if it is enough to separate user privileges by Role.

---

1. Within the tables images and users, you will be referencing the clients id, not the name.
2. I would create a separate table for the galleries, as clients tend to have new wishes every three month. So you maybe need to add more galleries.
   • table "galleries"
     • id
     • name
   • table "image_is_in_gallery"
     • image_id
     • gallery_id
     • PRIMARY(image_id, gallery_id)

---

You might want to consider normalization.

Assuming that usernames are unique - two people can't have the same username, come on - then you can eliminate "id" in the Users table in order to help prevent update/insert/delete anomalies (doing this would almost certainly put Users into BCNF, and likely DKNF - this is a good thing).

Clients is fine. What is the difference between Clients and Users, though? Really... seems similar to me.

Make sure that references are done using foreign key constraints, and I think that should be better.

EDIT:

Based on the new design, I have these suggestions:

Change Clients/Users into three tables:

  ClientNames
  - ClientID (PK)
  - ClientName

  ClientUsernames
  - ClientID (PK)
  - Username

  UsernamePasswords
  - Username (PK)
  - Password

This is safe and says that one Client/User has one name, one Client/User has one Username, and one Username has one Password. I don't see another good decomposition (in the sense that it's going to be in a tight normal form).

You can eliminate one of these tables by eliminating the synthetic "ClientID" key, if you want. There are disadvantages to this, and it may not be possible (some people do have the same name!).

The problem here is that it is likely that ClientID, ClientName, and UserName determine each other in a way that isn't amenable to stuffing them in the same table.

---

1. use client id instead of client_name on the images and users table
2. Add another table, portfolio with at least name and id columns
3. Add another table, portfolio_images with two columns, image_id and portfolio_id. This will allow the feature mentioned by @Alex in the comments

response to edit
You can do the one image in multiple portfolios by querying PORTFOLIO_IMAGES and JOINing with images or portfolios as necessary. For example, if you want to display the wedding portfolio (psuedo-code)

SELECT filename,... 
FROM images img
INNER JOIN portfolio_images pimg on img.image_id = portfolio_images.image_id
WHERE pimg.portfolio_id = <whatever the id is for wedding portfolio>