got this table that I need to put into a db so that when 2 values are selected it gives you the result i.e colum 120 and row 1.5 would give you 2, im pretty new to this so any help would be great do i need 2 tables im not sure?

Attachments table.jpg 403.43 KB
6 Years
Discussion Span
Last Post by BitBlt

No, make RowNum, ColNum primary key, CellValue a non-key column. No rule that says a key column has to be an integer, so you could have your RowNum be float or real. Or just have a non-significant key column, with RowNum, ColNum be an alternate unique key. Query as "select * from myTable where RowNum = 1.5 and ColNum = 30" or something like that. Easy as pie.

Edited by BitBlt: Additional thoughts.


ermm thanks for the reply but I dont understand, so you are saying have 3 columns RowNum, ColNum and CellValue. Its the second bit when you talk about float and alternate unique key? sorry for being a bit slow


No worries. "Float" is a datatype that can contain decimal values, as opposed to Integer which can only contain whole numbers. I specifically used the physical characteristic (rather than just pure logical characteristics) to call out that your example data needs to accommodate decimal values.
Here's some pseudo-SQL to help:

create table myTable
(RowNum float not null,
ColNum float not null,
CellValue float null
primary key (RowNum, ColNum)

Or, for the second alternative:

create table myTable
(myTableId integer not null,
RowNum float not null,
ColNum float not null,
CellValue float null,
primary key (myTableId),
unique index (RowNum, ColNum)

Some purists argue that integer datatypes for the primary key help performance, or that you should never have a "natural key". I'll leave it to you to research why if you're interested.

The idea behind making the unique index here is to make sure you can only have one CellValue for any given row/column combination.

Anyway, hope this clarifies. Don't forget that the above examples aren't real SQL. You'll have to translate to the appropriate syntax for the database you're using. Good luck!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.