Member Avatar for dandixon

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?

table.jpg 403.43 KB
  • 2 Contributors
  • 3 Replies
  • 113 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by BitBlt

Dani AI

Generated

Useful start from — modelling the grid as rows in a table is the right direction. A few practical points to make that model robust and avoid surprises down the road.

Binary floating types can behave unpredictably for exact-match lookups (two decimal values that look the same in input may not compare equal in the database). For any axis value you will match exactly, use a fixed‑point type (DECIMAL/NUMERIC) or store scaled integers (for example, multiply values by 10 or 100 to avoid fractional storage). See the notes on floating‑point pitfalls for background: Floating‑point arithmetic - Wikipedia and DB vendor docs for numeric types such as PostgreSQL numeric or MySQL floating‑point types.

For a cleaner schema and better indexes, consider normalizing the axes into lookup tables (RowAxis and ColAxis: id + value) and then storing the grid as a mapping table that references the axis ids and stores the cell value. Benefits: small integer keys in indexes, enforced referential integrity, simpler uniqueness constraints on the (row_id, col_id) pair, and easier maintenance if you later add/remove axis values.

If the application needs values between defined grid points (interpolation) that is not handled by equality lookups: fetch the bounding neighbors (the next lower and next higher row/column values) and compute interpolation in the application or with SQL expressions. Test queries on realistic data and add the appropriate composite index to keep lookups fast.

Quick checklist: pick an exact numeric type or scaled integer for axis values; use FKs to axis tables; enforce uniqueness on the row/column pair; test equality behavior and query plans with sample data. These steps will avoid subtle bugs and make the grid easy to query and maintain.

Recommended Answers

All 3 Replies

Member Avatar for 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 because: Additional thoughts.
Member Avatar for dandixon

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

Member Avatar for BitBlt

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!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.