Storing Multiple Ranges per record...thoughts?

[mmdonloaf]

Hi, I'm very new to this so your patience and help is requested.

I need to store multiple ranges per record in my relational db, i.e.

A: 3500-4800, 5230 (spot) , 6400-7900

Etc, I need the data to be searchable with minimal fuss and coding.

Any help would be very very welcome.

Cheers

 

[boblarson]

Your question is a contradiction in itself.

If you are using a relational db then you do not store multiple data within a single field within a record. That is a violation of normalization rules and you also say,

I need the data to be searchable with minimal fuss and coding.

and that is not going to be valid if you store the data that way.

What you need to be able to do is think in relational database terms. That means that for this data you would need at least one, if not more, additional table to store the multiple items that would be valid for a certain entity. In other words you are looking at a one-to-many situation.

I think a lot more information regarding what data you are trying to store, what you need to get out of it, what your business processes are, etc. is needed to give you clearer direction on where to go and how to proceed.

 

[ajetrumpet]

MMDONLOAF,

I was referred to this link for reading material, I learned a few things from this about Relational DB's....

http://r937.com/relational.html

 

[Duikmeester]

Hi, I'm very new to this so your patience and help is requested.

I need to store multiple ranges per record in my relational db, i.e.

A: 3500-4800, 5230 (spot) , 6400-7900

Etc, I need the data to be searchable with minimal fuss and coding.

Any help would be very very welcome.

Cheers

One solution would be like follows:

table: records
record_id   record_description
1           MyDescription

table: record_ranges
range_id    record_id    range_start    range_end
1           1            3500           4800
2           1            5230           5230 OR NULL (indicate spot)
3           1            6400           7900

 

[KKilfoil]

One solution would be like follows:

table: records
record_id   record_description
1           MyDescription

table: record_ranges
range_id    record_id    range_start    range_end
1           1            3500           4800
2           1            5230           5230 OR NULL (indicate spot)
3           1            6400           7900

Be VERY careful allowing NULL as a 'valid' value in a record. I'd use the 5230 value (or a 'special' value like -1). There's some recent discussion on the 'theory' forum if you want to know why.

 

[boblarson]

There's some recent discussion on the 'theory' forum if you want to know why.

I also reposted some excerpts from The_Doc_Man that he posted in response to the question of using NULLS as values in the FAQ section.