Need Help with developing table structure

cdoyle

Registered User.
Local time
Today, 11:10
Joined
Jun 9, 2004
Messages
383
HI,
I've been banging my head on this, and I just can't seem to find a good way to create my tables.

They want a selection with a sub selection, and then allow the user to fill in data depending on that data. I'm not sure if I should make a new table to hold the data for each sub selection? Or should I make 1 larger table with columns for all the sub selection data fields, even tho they won't all get filled in for each record.

Option #1
Option #1 sub choice
Sub Choice 1​
Sub choice 1 fields​
Dates, field 2, field 3​
Sub Choice 2​
Sub Choice 2 fields​
Dates, Sub Choice 2 fields​

Option #2
and same structure as before, just different options.

this is so hard to explain, not even sure I should post this.
 
If you think it's hard to explain, try trying to answer this with only your explanation to go on! ;) The best I can do is a very generic concept that may or may not be relevant to your situation.

Ok. Let's think about this. You have a table that represent an entity. Maybe an order, maybe a document, whatever...I don't know cos you haven't told me.

tblEntity
EntityID (auto,pk)

You have two 'levels' of options. Selection and SubSelection

tblOptionType
OptionTypeID (auto, pk)
OptionTypeName (Text)

And an entity has more than one option associated with it, so:
tblEntityOptions
EntityOptionID (auto,pk)
EntityID (FK)
OptionTypeID (FK)

You want to store different kinds of data. Let's have a table for the various types of data.

tblDataKinds
DataKindID (auto, pk)
DataKindName (Text)

You want to store different kinds of data about the selection versus the subselection, so let's have a table that stores information about what kinds of data you want to associate with what type of Option.

tblSelectionDataKinds
SelectionDataKindID (auto, pk)
OptionTypeID (FK)
DataKindID (FK)

This table allows you to setup which DataKind records show up in a combo box based on whether the user-chosen OptionType is a Selection or a Subselection.

Then we need to have a place to actually store the information for each entity.

tblEntityOptionData
EntityOptionDataID (auto, pk)
EntityOptionID (FK)
DataKindID (FK)
DataResult (Text)

Because the datatype of the information could sometimes be numeric, sometimes date, or sometime text, you have to use a data type that can handle all options in the result field. If the 'result' will always be numeric regardless of the DataKind, then you could change that.

Hope it gives you some food for thought. Or maybe encourage you to offer a bit more information. :)
 
Thanks, sorry I didnt' give enough info.

Something I left out is, all this information would be related to a record in my main table.

It looks like I might be on the same path of you,
I have my main table.

I then created a 'criteria' table that has

Criteria_ID PK
Main_ID FK from Main Table
Criteria_Type >>>number field, data from 'criteria type table'
Criteria_Sub_Type>>> number field, data from criteria sub level table'

I then created sub tables for each sub topic.

example
ID pk
Criteria_ID FK from Criteria Table
Field 1
Field 2
etc.

So I ended up with like 10 tables I believe.
It's just so many tables, I'm wondering if this is right?

I added the relationships between all these, and then added the relationship to the main table from the criteria table.

If I have this right, I have to figure out how to get all this on a form! Another problem I'm running into is, say if they selected Option #1, they need to have the ablility to make a sub selection and fill out the data, and then make another sub selection, under option #1 and fill out the data for that too.
 
Last edited:
tblEntityOptionData
EntityOptionDataID (auto, pk)
EntityOptionID (FK)
DataKindID (FK)
DataResult (Text)

I'm a little unsure what DataResult is?
 
DataResult is whatever the information is that they type in.

Table DataKind stores the equivalent of the field headings
 
So instead of having a table with 3 fields for:
Dates
Provider
Whatever else they want.

I would just have the 1 field? Where they could type in all the data into it?

Or am I misundertanding what you mean?

If that is what it's for, I see a problem later on with reporting. This field could become very filled with different types of data, dates, provider names, all the drugs used.

I think on a report, they would want these fields seperated, so they can skim through it.
 
Last edited:
As I said, it's a very generic solution that may not work well for you. Not knowing what kinds of data you want stored, or how they relate in the real world, it's hard to comment more specifically.

That said, you can always 'split out' the 'fields' in a crosstab query and base your reports on the crosstab.
 
I'll try and explain a little more to what I have, and what I need to do.

I have a member table

tbl_member
MemberID PK
Member_id_number
name
etc

tbl_Main
Main_ID pk
MemberID fk
date_loaded
loaded_by

I have some other tables that relate to this main table, that hold other information, not really needed for this.

Then I have this 'Criteria' data that I need to somehow come up with a way to store.
It all relates back to the Main_ID of tbl_Main

I created a table to hold the 3 main option choices
tbl_Criteria_dropdown
Criteria_dropdown_ID pk
Criteria_dropdown (text)

I also created a tbl for the sub choices (not sure if there is a better way to do these last 2 steps.)

tbl_criteria_sub_lookup
ID PK
Criteria_dropdown_ID (number field, I just typed in the number ID from tbl_Criteria_dropdown, to help filter the drop down later on)
Criteria_Sub_Options (text for dropdown)

Now what I did was created a new table for each sub category option.

So,
tbl_option_1_subcategory_1
Criteria_ID
dates
provider
etc
tbl_option_1_subcategory_2
Criteria_ID
dates
RX
etc

tbl_option_2_subcategory_1
Criteria_ID
Dates
Fees,
Etc.

Those aren't the real names of the tables, just trying to simplify it some.

An example of how it could be entered by the user, they could select option #1 subcategory #1 and fill out the data. They need to then be able to also select subcategory #2, 3, or 4 and fill out the info for that too if they need too.

Does that help at all? Does what I'm doing make sense, and seem like the right approach?
 
Last edited:
I understand a table to store people because there're real world people you need to track. Some may or may not be members.

tblPeople
PeopleID (PK)
PeopleFirstName
PeopleLastName
etc

I undertsand the concept of Membership.
tblMembers

MemberID (PK)
PeopleID FK
SignupDate

I understand the concept of membership being a collection of people that changes through time. This too requires a table.

tblMembershipPeriods
MemberShipPeriodID (PK)
MemberID FK
StartPeriodDate
PeriodLengthMonths


However, I don't understand anything about your 'Main' table because I don't know what it represents in the real world. I also don't understand what this criteria date is all about either.

Please understand, I'm not trying to be nosey, but the real world data model is what should drive your table design. Beyond the generic design I already gave you I cannot really advise you without knowing what the real world situation is.

EDIT: Something else occurs to me also. Are you sure that the issue you're describing isn't something that can be solved using cascading combo boxes on your forms?
 
Last edited:
tblmain is the many side to my tbl_member

So it has fields like this.
Date_loaded>> date that this record was created
Loaded_by>> user who loaded this record 'UserID'
Comments>>Text field to allow them to write comments about this record

The dates in the criteria sections, are related to that sub category.
So if they chose Option #1 sub-category#1.
The 'dates' field are dates to which they went to see a provider.

subcategory#2 also needs a place for dates that relate to this catagory. For this option, the 'dates' field could be when they were issued a drug

Does that help?
 
I've thought of cascading combos, and I think once I get to the forms I'll be using them. But I'm still not sure how to design the tables and how to store the data for all these categories.

A part of me, just wants to be lazy and create 1 big criteria table with all these fields, knowing that most cells could be left blank per record. But I'm trying to do this right, and break things down better.
 
tblmain is the many side to my tbl_member

That still doesn't tell me what the records in the table represent in the real world. I tried to demonstrate the process of describing what real-world entities are represented by tables in the last post.

I still think the approach I detailed in the first place would work for you. If all these criteria are date datatypes then change the datatype of DataResult to date/time.

If I have time tomorrow morning I'll whip-up an example db for you to demonstrate what I mean.
 
I would really appreciate it, maybe if I see an example it would make more sense to me.

All the criteria will not be just date datatypes, the dates are only 1 piece of each sub category, they need to be able to store: provider names, drugs, provider numbers, it just depends on which sub category they pick, to what info they want.
 
Okay, here is an example. Remember, this is NOT going to be an exact fit for you because I'm not anywhere near as familiar as you with your real world data model/constraints. This is just to show you how you can take a fairly generic solution and use it, perhaps, in your situation.

This uses crosstab queries to provide sources for reports. The example data is fairly limited. There are three memberships with one or more people (like fmaily coverage for example). Each membership can make multiple 'selections'. For each selection, there are multiple pieces of information that can be stored. Each piece of information has a datakind (equivalent to a field name) and the data value (dataresult).

Reports are set up for each selection option, based on a fixed-column crosstab query that also uses MemberID as a criterion. The crosstabs basically create the 'fields' you want dynamically and the reports show those fields (nulls will show up if the equivalent value wasn't entered). Please note that example reports for only the first two selection options have been set up.

The db is also nowhere near complete.

It could be extended also. For example, I've included a table of suggested values to populate a combo box for results depending on which datakind is being entered. You could, instead, store a sql string instead of a value and set the rowsource for the combo to match that sql string. This would allow you to query fields in tables of entities like Providers, Drugs, etc that could appear in the results combo.

This is solely to try to demonstrate one possible approach. It may be so far off the wall you should scrap it and see if you can better document your real world data system so that a more specific targeted data model can be drawn up.

For this, you need to explain the big picture of the real world situation as well as the business rules etc. For starters, explaining exactly what line of business you're in would be helpful, as well as what processes you plan to use to populate the db (where the information comes from....paper forms, output from other dbs etc), and what you need to produce as outputs.
 

Attachments

That's pretty cool, I'm going to play around with it, and see if I can make it work for what I need.

Thanks!
 
After reading this thread more than once, I see nothing clear about the nature of the real world problem. I must offer these comments.

First, this is an example of a question that supplied almost NO description of the problem overview. Therefore, it is not even SLIGHTLY possible to understand the nature of the details to be stored.

Second, on repeated questioning, only abstract issues were ever mentioned. Banana and I went through a very long thread about abstractions and how far you could take them. You might wish to read up on that issue.

Third, by being so very amorphous, you negate ANY CHANCE of normalizing the database, which will make your life impossible down the road.

The questions you SHOULD be asking are what you want to see later, what will you need in order to construct your report, what will you need to keep proper history of all of these selections, and what are you really building here. There was a really great post on how not to ask a question. I'm sorry to say that in terms of the data presented for the problem statement, this is an example of how NOT to ask for help. Please don't take that as an intent to insult you, but please learn to organize your thoughts into something less vague before following up or asking other questions.
 
I think people sometimes forget, we can't provide all the information that you would like to see. I would love to post every bit of the DB and all the documentation that I have for you to look at, but I'm not allowed too. So I tried to describe the problem I was seeing with the documentation in a vague way, and not release any information that I should not be.

I tried my best to describe what I have been given to work with, and what they want to be able to do with it.

I'm sorry you felt the question I was asking was not organized, but all I was trying to do was describe the issue in a generic way, and hoping that someone may read it, and maybe had a similar issue with one of their databases in the past, and what they did to overcome it.

Craig, thank you for your example it does give me some ideas on how to proceed. I appreciate your help and also posting a sample DB, I was unclear by your description of what you had in mind, but your sample made it all make sense.
 
...but I'm not allowed too. So I tried to describe the problem I was seeing with the documentation in a vague way

It might have helped a little if you simply stated that you were not allowed to be more specific when responding to my questions rather than just ignoring/glossing over them.

I understand about not being allowed to release sensitive information but, a detailed description of your line of work and information processes/requirements IS essential to constructing a normalized database. DocMan is right on the money (as always). The data model is a reflection of the real world model. If you don't understand the latter, then you're screwed trying the make the former.

My guess is that you're in some kind of health insurance business? The fundamentals of that line of work are not unique to any one health insurance company. The essentials KINDS of data you will need to track will be the same across the industry. There's no secret info lost by discussing them openly. Competing companies already know all that stuff. Most already have databases that do the job. There's a world of difference between describing your business model and discussing your business name, clients, etc. By knowing what line of work you're in, it allows us to ask better questions and see potential pitfalls using our own understanding beyond the information that you provide. Heck, there are even people on this forum that have built databases for other health insurance companies in the past.

My advice for your 'superiors': if your business model itself is so top secret, then they need to retain a database consultant/professional's services where a non disclosure agreement can be signed.

In any case, I'm glad the example db gave you some ideas to move forward with. I think the point of DocMan's post is that this type of abstract solution has its own set of issues to deal with. In the end, a more specific data model would serve you much better but this could never be obtained without a specific, careful description of your business requirements/constraints, and information processes.
 

Users who are viewing this thread

Back
Top Bottom