Q&A Database Design


(Kenold Beauplan) #1

Need suggestions for a Q&A database design that I will use to interview small business owners. Below is a screenshot of the project and the tables that I came up with. Wanted to know which table should I add the introduction, video and audio urls to?

[People Table]
id
first_name
last_name
job_title
company
photo_url

[Questions Table]
id
question

[Answers Table]
id
person_id
question_id
answer
order_no

(Matt Andrews) #2

Do you have a fourth table tying this all together? If I come to your site and visit yoursite.com/q-and-a/123, what table is being queried for id=123? Is it all keyed off the people table? In which case, I’d add those fields there. Otherwise, make a fourth table (articles maybe?) which holds all the info linked together, and allows you to do a second Q&A with someone who’s done one before?


(Kenold Beauplan) #3

I didn’t have a fourth table, it’s all keyed of the people table. How would the fourth table look like?


(Matt Andrews) #4

It’s your call – the only advantage of a fourth table would be the ability to re-use a person (eg. do another Q&A with them at a later date). But you could do something like:

[article Table]
id
person_id
introduction
video_url
audio_url

…then when someone visits yoursite.com/q-and-a/123 you’d be querying article for id=123 and JOINing on people, questions and answers.

Might be overkill though – right now you could just add those three columns to people and it’d be fine.


(Andy Wootton) #5

It’s conventional to call the tables ‘Person’, ‘Question’ and ‘Answer’ i.e. the name of a singular item in the table. Your ‘person-id’ and ‘question-id’ field are the best example I’ve ever seen of why.

I’m not a database person so I find diagrams help me, either Entity-Relationship diagrams or UML class diagrams. Not everyone agrees that they’re useful. It depends how your brain is wired, I think. You mark the relationships as one-to-many and if you get a many-to-many relationship, you may need different tables. Matt is pointing out potential m-m relationships between your tables, which is against pure relational theory. In reality, people break the rules to get better performance but hopefully that won’t be an issue with sensible sized databases.


(Jon) #6

If Video URL and Audio URL are “questions” in your context, then perhaps URLs are answers, in which case they should go in answers.answer. However, your answers table is looking like a key-values pair table, which can start to get annoying (and slow) if you want to run traditional SQL queries on it.


(Stuart Langridge) #7

So, to build a page with this DB layout, you’d need to do this SQL query (to do it all in one go):

select p.first_name, p.last_name, p.job_title, p.company, p.photo_url, 
    a.answer, a.order_no, q.question 
    from person p
    inner join answer a on a.person_id = p.id
    inner join question q on a.question_id = q.id
    where p.id = :id_from_querystring
    order by a.order_no

and you’ll get results that look like this:

first_name|last_name|job_title|company  |photo_url|answer|order_no|question
John      |Barnes   |Winger   |Liverpool|http...  |Pies  |1       |Fave food?
John      |Barnes   |Winger   |Liverpool|http...  |Red   |2       |Fave colour?
John      |Barnes   |Winger   |Liverpool|http...  |Keegan|3       |Fave manager?

That is: you’ll repeat the “common” stuff in every row, which is pretty annoying. Alternatively you’d do two queries:

-- header query
select p.first_name, p.last_name, p.job_title, p.company, p.photo_url, 
    a.answer, a.order_no, q.question 
    from person p
    where p.id = :id_from_querystring

and

-- questions and answers query
select a.answer, a.order_no, q.question 
    from answer a
    inner join question q on a.question_id = q.id
    where a.person_id = :id_from_querystring
    order by a.order_no

and then you’d get two responses, one to do the page “header” which describes the person themselves, and a second to list the question/answer pairs. Done like that, I think it’s reasonably obvious that introduction text and video and audio urls belong to the “person” and therefore go in the header query, and it’s fine to just add them to the person table because them being large won’t affect any other queries.


(Kenold Beauplan) #8

Thank you. I could move video, audio and intro to the “Person” table, but these fields are part of the interview.
Ex: Each Q&A, may have a video profile of the person’s company (to display after each interview). So if that person changes company and does another interview, a new video should be added.


(Marc Cooper) #9

It’s also a convention to name tables “people”, “questions”, and “answers”. i.e. the name of the collection. i.e. what it is.

Rails uses this convention. The singular (capitalised) is reserved for the class name.

It’s a rare time that Rails got it right, imo.

(Note: rails is a legacy framework used by those yet to discover phoenix.)


(Stuart Langridge) #10

Ah. Then I’d have an interview table, if it were me. Define each separate “thingy” as a separate table. So:

interview table: id, person_id, introduction, video, audio, job_title, company
person table: id, name, photo
question table: id, question_text
answer table: id, interview_id, question_id, answer_text, order_no

and then your interview page has an interview ID in the querystring, meaning that you do

select p.name, p.photo, i.introduction, i.video, i.audio, i.job_title, i.company 
from interview i inner join person p on i.person_id = p.id
where i.id = :id-from-querystring

to get the “header” and

select q.question_text, a.answer_text 
from answer a inner join question q
on a.question_id = q.id
where a.interview_id = :id-from-querystring
order by a.order_no asc

to get the questions.

Couple of thoughts:

  1. this assumes that you ask mostly the same questions to each interviewee, which is the impression I get from your original schema
  2. don’t have first_name and last_name. Just have name. Srsly. See https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/ for why in excruciating detail.

(Kenold Beauplan) #11

This makes more sense now with the Interview table. Also thanks for the link about names :smile: I’ll try it later and will write back if anything.


(Andy Wootton) #12

But people_id wouldn’t make sense in this context because it’s the ID of 1record. I wouldn’t normally mention it but I actually found it confusing to read.


(Kenold Beauplan) #13

Laravel uses the same naming convention. Table (migration) names are plural, class name is singular.


(Andy Wootton) #14

How does this work in a mixed-language environment, sharing a relational database? Different conventions at the relation level seem a bad idea to me. A data structure doesn’t belong to any one language.

As I’ve said before, I’ve never coded in OO languages but I know there are difficulties about objects using relations as their persistence model. Is ‘collection’ an abstract language structure that is then mapped onto a relation? Do ‘frameworks’ like Rails do this automatically?


(Marc Cooper) #15

Naming doesn’t change structure. It’s just names.

Dbs map to objects via ORMs (often). There’s no coupling beyond the language or framework’s abstractions.

Objects don’t use relations as their persistence model. Relationships are often modelled as associations, but they don’t always map to FK bindings. It’s just a bunch of abstractions.

A collection is just some things. It might be empty. It might be only one thing. (I’d call it a set (mathematically), but a set has semantics in this context.)


(Marc Cooper) #16

They can, kind of.


(Andy Wootton) #17

If there was an old COBOL program doing the monthly sales figures (I’ve never worked in such an environment but I understand they do exist) and my wireless, Internet of Things cash pouch and build in RF scanner, designed to manage cash flow and re-ordering in the shoe-shop, needed to feed a transaction into the overnight batch run sales figures, could I use the ‘sale’ table and only call it ‘sales’ in the Ruby object mapped onto it?

I think it’s a difficult balancing act to decide when to respect protocols you don’t believe in but the alternative is anarchy and incompatibility.


(Steve Jalim) #18

Any half-decent web framework should allow you to map arbitrary model names to any table name you please, else Hot New Framework would be untenable for a legacy system with crazytown table naming, for instance.

Django example:

class MyFoo(models.Model):
  
     name = models.CharField(max_length=45, blank=True, null=False)
     ...

     class Meta:   # metadata re the model/object's quirks 
         db_table = 'bar_foofooFOO'   # ie, doesn't matter what the DBA was drinking that day

  

(Marc Cooper) #19

Aye, Rails is:

class Product < ApplicationRecord
  self.table_name = "my_products"
end

or Sequel:

class Product < Sequel::Model(:my_products)
end

(Andy Wootton) #20

Exactly, so there are different conventions at different levels of abstraction. At my BA-hat conceptual level, it’s easier to talk to the DBAs if I stick to their relational conventions in case I need their help to understand the mapping between the people who understand the business problem and the data that is already available. When the space-cowboys, schooled in the latest Cool Thing of the week come in, they can talk whatever trendy street-jive they need to and be protected by framework magic :-D.

Obviously Agile ruins the natural balance of things because we sometimes need to let devs talk to real people too.