时间戳

首页 > 解决方案 > How should I design my relational tables?

database - How should I design my relational tables?

问题描述

I am curious to the practicality of structuring my database. Imagine I want to conduct a survey, asking a group of students what are their top 3 preferences out of 10 options. The objective is to find out ranking of the 10 options by looking at the count of students' choices.

Initially, my thought process is to create the following relational tables.

CREATE TABLE person (
    personID BIGSERIAL NOT NULL PRIMARY KEY,
    personName VARCHAR(50));

CREATE TABLE preference (
    preferenceID BIGSERIAL NOT NULL PRIMARY KEY,
    personID INT REFERENCES person(personID),
    preference1 VARCHAR(50),
    preference2 VARCHAR(50),
    preference3 VARCHAR(50));

But I begin to stumble on achieving my objective. Since the count of each preference has to be considered in all 3 columns, my current solution is to then manipulate my data (using Excel) until I have the following table:

CREATE TABLE preferenceV2 (
    personID INT,
    preference VARCHAR(50));

Here, personID is no longer PK nor is it unique, and preference can be either of the 10 options. Now, I can achieve my objective by counting the number of votes for each preference and rank them accordingly (through use of PowerBI for example).

However, I realised I end up not leveraging on relational database and the process of manipulating into table preferenceV2 is manual and prone to errors. What should be the best way for me to approach this?

标签： databasepostgresqldata-structuresrelational-databasepowerbi

解决方案

你应该有：

CREATE TABLE preferences (
    preferenceID BIGSERIAL NOT NULL PRIMARY KEY,
    personID INT REFERENCES person(personID),
    preference VARCHAR(50)

因此，如果一个人有 3 种偏好，您将拥有

   preferenceID personID preference 
        1          1         A
        2          1         B
        3          1         C
        4          2         B
        5          2         C
        6          2         D

在这种情况下，你可以做

  SELECT preference, COUNT(preference)
  FROM preferences 
  GROUP BY preference

推荐阅读