Re: Question regarding keyword checkboxes in database design - Mailing list pgsql-novice

Lynna Landstreet wrote:
 >
 > the current structure where the keywords in any given set (i.e. subject,
 > medium, etc.) are all lumped together into one long string for each
record
 > would be considered bad database design

Again, I'm no expert either but I would definitely say that is bad
design. Strangely enough my current job is also converting a FMP DB to
postgres and I had the same issue. Many data clumped into one field.

 > But if I make each keyword into a boolean field, I'm not sure how
 > to display them.

That's not a DB issue. In your case that's a web browser issue and
related to whatever web programming language you will use.

 > Pg usually return the data in the columns, not
 > the column names.

The data returned is associated with a column name so you always know
where your data came from ;)

 > When people ultimately view the database records on the
 > web, I want them to be able to see something like:
 >
 > Medium: black and white photograph
 > Subject: landscape, nature
 > Processes: hand-tinting, photocollage
 >
 > Or something to that effect, but NOT:
 >
 > black and white photograph: yes
 > landscape: yes
 > nature: yes
 > hand-tinting: yes
 > photocollage: yes

The only difference between the two versions you show here is that in
the second one you don't show the column names, but of course you (the
programmer) know what they are since you did the SELECT that fetched the
data ...

 > So I'm thinking that probably each set of keywords (medium, subject,
etc.)
 > probably needs to be a table {...]

Hum, database design. A thorny issue for the newbie. The way you decide
to go will affect many things so plan carefully.

I don't know enough about your data or database design to suggest
anything but from what I can gather of your data you would have an
exhibition/art piece (?) table in it.

Then you would have a table for, say b/w photography and in that table
you would have one entry for each art piece that fell into that
category. And so on for all the other kinds of possible ways to
categorize the art piece.

So the art piece table would no contain any information at all on what
kind of art it is. To find that out you would need to search all the
possible category tables to see if there was a matching entry for that
art piece.

I think someone may suggest that a view would make you life easier if
you did decide to go that route ...

 > but I'm not sure if there's a way to modify a select statement so that it
 > outputs the names of all columns with a yes/true/1 in them, rather than
 > outputting the values themselves.

If you go with my design you don't knew to output anything. If there is
an entry for that art piece in a particular category table then it's of
that category.

 > BTW, I'll be using PHP to create the front end
 > of this for the web site, if that makes a difference.

I'm using PHP too. So far so good ... I did have to dump out all the FMP
data into one big text file and create a custom PHP script to parse the
data and insert it into PG tables and make the necessary relationships
though.

In my case I did have the same serial issue you mentioned in your last
question but since the primary keys in FMP weren't used for anything
else but keys into tables I didn't need to actually keep the same key
id. I just found all the data in the text file that matched on that key,
parsed it, inserted it into PG, let PG assign a new serial and used the
assigned serial to create the relationships in all the other tables.

HTH,

--

Jean-Christian Imbeault