Suggestion Anyone

kschuster

Working with Mysql5

Trying to figure out the best way to structure this database.

basically I have a table like this

fields:

stateid
taxable (y/n)
taxsoftware (y/n)
taxinventory (y/n)
taxCIP (y/n)

I need to allow the users to attach comments to each of the columns. I need to strore the comments in a separate database where each users comments about a specific field can be captured.

What is the best way to go about this? Some kind of match table? Can't visualize how that would be queried.

Houdini

Well either as an ENUM with values of Yes, No or maybe a BOOLEAN with0 (false) or 1 (true).

devinemke

for booleans i typically use a CHAR(1) field that is either Y or NULL. as to the comments table: create a table that has a primary key ID and all of the same field names as this table. use the stateid as the foreign key in the comments table.

kschuster

I think you missed the question

for the column TAXSOFTWARE I want to be able to create comments associated with that column - or any of the others listed. This would happen in a separate table. How do you link a column and a row to another table

dougal85

That sounds gay. start again.

First table needs to be changed for any proper normalisation.

devinemke

kschuster wrote:
I think you missed the question

for the column TAXSOFTWARE I want to be able to create comments associated with that column - or any of the others listed. This would happen in a separate table. How do you link a column and a row to another table

no i didn't miss the question. that is preceisely what i suggested. create a separate table that has the corresponding fields in your first table that wil store comments associated with each field. again, use the primary key field from the first table (presumably stateid) as the foreign key in the comments table to link the two.

i think what dougal85 is getting at (in a very politically incorrect way) is that there seems to be no need for the second table at all. simply add more fields to your first table to store the comments.

barand

kschuster wrote:
I think you missed the question

How do you link a column and a row to another table

You don't, you link rows. So as dougal85 suggested, change your first table so you have a row for each taxtype

Table1                Table2
--------              -----------
stateid      <-----   stateid
taxtype      <-----   taxtype
status(y/n)           recid
                      comment