CODEWITHSUNDEEP
phpmysql
Nazim Iqbal
Nazim Iqbal

Reputation: 21

Complex MySQL database

I have an Art Gallery project.

Every hour a group visits the gallery, which means 24 hours = 24 groups.

Sometimes more than 24 groups visit, for example 25, 28, 30, 40...
Every group has 50 to 60 or 70 visitors, each with Unique ID’s generated by the system.
I have to design a database in which I can insert all the group IDs according to the hour visited.
Example:

1 hour = 1, 34, 3, 6, 67, 53, 57, ... up to 60 or 70 total IDs
2 hours = 2, 35, 4, 7, 68, 54, 58 ... up to 60 or 70 total IDs
...and so on.

For a day and the next day the same process is repeated with other unique IDs.

If I create a table for hours then I have NULL values for the other hours, which results in useless space!
If I create different tables for different hours, then what if I have more hours than the number of hour-tables I have?

Can anyone suggest how to design a perfect table or some other way to insert values without NULL values?

I am using PHP with MySQL.

Upvotes: 0

Views: 420

Answers (2)

jolivier
jolivier

Reputation: 7635

You are typically in front of a 1:N entity relationship between

  • the visit hour
  • the unique id since each hour multiple visitors can have visited the gallery.

This problem is solved by having a 2 column table

  • the visit time (storing the hour and the date in a DATETIME column for instance)
  • the unique id (storing the unique id of the visitor, as an UNSIGNED INT or other data type depending on how your unique id are formed).

It is then the responsibility of well written SQL queries to present the data as desired by the user, for instance: SELECT `unique id` FROM `yourtable` WHERE `visit time` = '2012-07-22 15' could show you the list of unique ids for 15h today.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You should create your database to model your business problem.

Create a table for each visitor. This table will have a primary key, which are are calling the UniqueId.

Create a table for each group. There would be one row per group. Call it something like TourGroup, rather than "group" since "group" is a reserved word in SQL. This table would have a TourGroupId, and information about the group. From what you describe, each group only visits once, so the hour of the visit would be in this table.

Create a table that assigns visitors to groups. Call this something like VisitGroup. It would have two important columns, the TourGroupId and the UniqueId.

This process is called normalization. In general, when using databases, you are best off with a data structure that mimics the real world situation.

One more piece of advice. In every table, have the id be the primary key and the first column in the table. It should be auto-incremented. Also, include two columns at the end of the table, CreatedBy and CreatedAt. These should have default values of who and when each row was created. This takes up a bit of extra space in the database, but will be immensely helpful in the future to help resolve problems.

Upvotes: 3

Related Questions