How would you represent this data in a dataset?

[Slow_Learner]

I'm working with some data that I'm having some trouble finding a good structure for. The data is something like this:

SomeName(String, key); SomeCategory(String); VaryingSubfield(see below)

VaryingSubfield can contain zero or more record-like data entities (record-like in themselves) of the rough format:
SomeString2(String); SomeString2(String); .... SomeString8(String)
The values for this "record" are not required to be unique.

I have considered making VaryingSubfield a relation pointing to a separate table, but I'm not sure how to represent VaryingSubfield if it contains more than one item without some really hackish stuff (e.g. an array with the contents being row IDs to the child table). I'm fairly comfortable with non-relationable tables but haven't spent a lot of time with relational tables.

This may be really easy, I dunno, but I have been scratching my head over this for a while now.

[Edneeis]

How is this data related? Is this a parent/child structure?

SomeName
PK_SomeNameID
String
Key

SomeCategory
PK_SomeCategoryID
String
FK_SomeNameID

VaryingSubField
PK_VaryingSubFieldID
String
FK_SomeCategoryID

Your explanation seems more like a class structure but I don't understand how the items are related.

[Slow_Learner]

I'm just talking about the data right now, at this point the record format isn't even set. I have a big mess of data in text files that are poorly delimited and are very painful to work with and I want to come up with a better record structure.

Let's see, some sample data would look like:
-------------------------
Name: BigBoxOfFruit
Category: Container
Contents:
Cherry, Red, Round
Banana, Yellow, Oblong
Apple, Red, Round
-------------------------

-------------------------
Name: MediumBoxOneFruit
Category: Container
Contents:
Mango, Brown, Round
-------------------------

-------------------------
Name: SmallBoxNoFruit
Category: Container
Contents:
-------------------------

What I'm having trouble getting my head around is how to represent Contents if it contains more than one item. Zero is easy, one is easy.

[hellswraith]

Table One:
OneID (primary key)
NameColumn
CategoryColumn

Table Two:
TwoID (primary key)
OneID (foreign key)
Content

Have a one to many or none relationship between table one and table two. You can add new contents to table two, and as long as the foreign key is from table one, it will be associated with that record.

If you don't understand what I mean, I will whip up a db diagram for you if you need it.

[hellswraith]

I made it easier to understand:

[Slow_Learner]

Hmm, I guess I need to go puzzle over datarelationship docs for a while. I understand what you're saying at one level, but I still can't get my head around what's going to be in the parent field or what to do with it. I've worked with examples that use the DataGrid control but I want to understand how it really works. Wish I'd paid more attention in Data Structures in university. :/

Thanks for the info.

[hellswraith]

For each content item, there will be a record in the Contents table. So lets take your example:

Name: BigBoxOfFruit
Category: Container
Contents:
Cherry, Red, Round
Banana, Yellow, Oblong
Apple, Red, Round

You would have this record in the Main table I showed you:

MainID | Name             | Category
5      | BigBoxOfFruit    | Container

And these records in the Contents table:

FruitID |  MainID | TypeOfFruit
1       | 5       | Cherry
2       | 5       | Red
3       | 5       | Round
4       | 5       | Banana
5       | 5       | Yellow
6       | 5       | Oblong
7       | 5       | Apple
8       | 5       | Red
9       | 5       | Round

Now, when you want to figure out what the contents of the BigBoxOfFruit, you just need to do a simple SQL Statement to retrieve those values.
SELECT * FROM Contents WHERE MainID = (SELECT MainID FROM Main Where Name = "BigBoxOfFruit")

[Slow_Learner]

Oho, I think I get it. I keep expecting the "field" for contents (in the example) to show up in the parent table, but it doesn't does it. If the relationship is right, and the tool you're viewing with is aware of the relationship, then it just looks like the contents appear in a field. Is that right?

What if the order of the contents is important and needs to be preserved? (in my case it is and does) Does this work if the relationship is the same? MainID in the first table as a parent to MainID in the fruits table, child?

MainID | Name             | Category
5      | BigBoxOfFruit    | Container
6      | BoxOMelon        | Container

FruitID |  MainID | TypeOfFruit | ShapeOfFruit | ColorOfFruit | SizeOfFruit
1       | 5       | Cherry      | Round        | Red          | Small
2       | 5       | Banana      | Oblong       | Yellow       | Medium
3       | 5       | Apple       | Round        | Red          | Medium
4       | 6       | Watermelon  | Oblong       | Green        | Jumbo

[hellswraith]

Ya, that is what I meant.

If you absolutely need to keep them in some kind of order, it will be a little more difficult. If the order is like alphabetical, that would be easy because you can just use an ORDERBY clause in the sql statement.

[Slow_Learner]

Thanks both of you for taking time to explain that. I hate being so dumb about software I've used for 10 years.