CODEWITHSUNDEEP
mysqldatabase-designrelational-database
excowboy
excowboy

Reputation: 136

Relational database design for photography website

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

Upvotes: 2

Views: 4595

Answers (4)

Fabian Barney
Fabian Barney

Reputation: 14549

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

enter image description here

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.

Upvotes: 2

µBio
µBio

Reputation: 10748

  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>

Upvotes: 0

Patrick87
Patrick87

Reputation: 28292

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.

Upvotes: 1

feeela
feeela

Reputation: 29932

  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)

Upvotes: 1

Related Questions