Noob making complex relationships / Verbal Logic-Concept Resource
-
Hi All,
Below the Line I have a description of my very first database I am attempting to make: It is rather lengthy.
I have some key areas that I do not know how to best design the Tables/relationships.
Also, is there any resource that has very concise information on different relationships, when to use, why to use, expecially written as verbal logic statments/concepts, (not msdn please) as if a person was explaining it to you. All I have found is Examples of relationship and quick overviews/descriptions.
I suppose that I am looking for some gentle pointers in the right direction:
Senario: Set up with relations Primary to FK as I understand it.
* Client has Multiple Protocols
* Protocols can have may Study Ids.
* Study IDs can have multiple shipment locations
* Shipment locations can have multiple Study IDs
(Note I think that the Study IDs to shipment would be a multiple to multiple with a buffer table inbetween. Hard to find anything but and expample for this...not even concepts)Correct me if I am wrong.
* Study IDs can have Multiple Codes.... Codes vary as in 1 shipment can ship together code S1, S3, S12
and the same Study Id has another shipment that could shipt S5, S8 then to the same address S2, S15.
Also, a different Study ID could have a single code to multiple codes up to more than 100 differnt codes.
(The above is where I am at a loss for sure)
Alright, Take a Break, Even I am confused now despite the fact that I work with this all the time.
Then for the shipments:
* There is a contact (Attention To) per each shipment:
* There can be multiple contacts per Shipment Address.
* The shipment Address can have multiple email contacts
(Connecting previous * points from above, re-written below with more added)
Study ID + specific Codes can have multiple shipment addresses
Study ID + different Codes can have multiple shipment addresses
Shipment Addresses can have multiple Study ID + Codes
If you actually read this I am so terribly sorry, because to someone who actually knows more I am sure that this is a bunch of
Thanks
-
NOT a bunch of .... However, your request focuses on the crux of the issue. Being able to state clearly, in simple and direct language, what you are trying to do, will basically allow you to translate into a database structure. Your concern is what are the rules to follow in this verbal description. The following list is not exhaustive, but will give you some 'comfort'.
1. The M:M relationship normally requires an intermediate translation in a 'normalized' database.
2. Assuming you want to normalize your database to some degree, the challenge is to define a reasonable level of atomization or decomposition without going so crazy that for even a simple query you will need to reference many tables.
3. IF you can clearly map / draw / describe the detailed components under a 'Study', then you can probably have the initial database design drop into your lap.
4. Think in the following terms (like taking a cut jewel and turning it from one facet to another):
a. How will content be entered? Data entry, one study segment at a time, or will there be 'batched' entry via flat files or non-flat files?
b. What kind of information will need to be drawn out? Are all the reporting requirements 'known' at this time? Are there any 'exotic' requirements that do not seem to be straightforward as you understand them?
c. What kind of response time is needed? What kind of network impacts have to be 'allowed' for? [Clearly this sounds like a multi-user, multi-location application, but it may not be, from the data collection perspective]
5. You have a 'natural' hierarchy that must be supported by the database: Protocol > Study > Location > Shipment.
6. You will need to plan for referential tables. Bad Example: Location will have to support a Location ID (for internal use by the database aka Primary Key), Name, address information, phone (general switchboard?), contact person (Foreign Key? - there could be a 1:M relation between Location and contacts), etc...
Better Examples: 1. Mail Code translation: State Abbreviation, State (, internal PK?) 2. Country code, Country (, internal PK?)
7. You may need to capture 'supplemental' information, such as credentials for the investigators in the study, or a separate investigator:study relationship? vocabulary is critical here.
-
Many Thanks Steve,
#6 and #7 I got a bit lost on.
#4 iI have very good 3 dimensional thinking. I can see the problem from many angles with easy,but that is also the problem, I want it to be flexible enough that the future will allow new instances of what may become non-heirarcle. This is the majority of where my confusion lies.
#5. Even though it seems easy and hierarcle with plenty of one to many relationships, it is not that. 90percent of the time, yah, but not always.
One Departement will be using this software, but it is a rain drop in the whole company, and we are last in line, therefore we use information from so many buisness units...
What is a referential table? Do you mean tables that are relational twords another in varing manners?
What is "suplemental date": meaning where and what type of table would we be talking about to capture this data.
Bottom line:
I suspect that what I am going to have to do is create multiple m2m tables and relate the PKs to eachother in yet a M2M table.... So I would be relating a M2M for Shipments(containing Attent Too, email Contacts and shipping address) to another M2M table for Study OU Protocol.
And Here is my own advice... Start with half the problem, work out the M2M and then work on the second half.
Afterwards, then I would work on combining the 2 together.
Lastly, I saw you mention: "if you do want to normalize"
I suppose that some of this I could allow repetition of data to elminate some table complexity. The D-Base will never be terribly large.
-
Like Steve was pointing at, you should probably start with just simply drawing out your data "areas" (might be tables, might not be). Don't specifically worry about specific data fields at first - but keep them in mind. Start (in pencil) with a box for each group you know of:
clients
protocols
studies
shipment location
shipment addresses
shipment contacts
...Others i've forgotten...
Now - just simply draw lines between the ones that are related. As you draw them, describe the relationship visually: I usually annotate with a 1 just outside of the master box, and an infinity sign next to the child in a one-to-many.
Once you have that - if you have any lines with a many-to-many, just put another box in the middle of that line (your "mapping" table). Name it (I usually call them tbl_<table1>_<table2> when I'm mapping a rel between the two if I can't come up with anything more useful). do this until you have nothing but 1-1 or 1-M lines.
Next step is to take all of the individual, atomic data elements you need to capture, and put them in the correct boxes as you've drawn them. Go for the stuff you care about for now, and throw in a field called <table>ID which will be your primary key (what uniquely identifies each record in that table).
Next - the foreign keys. in the 1-M relationships, on the M side, add to its box the field which is the Primary Key for the 1 side.
Finally - you need to look it over again:
- Within each box - are there "hidden" relationships (like multiple e-mail addresses for each contact), you might need to break out, or can you live with having two e-mail fields per contact and have everything you need?
- Do you have the data showing up in multiple boxes (excluding the keys)? if so - break that out to a separate box, and start drawing lines again.
- Are the 1-1 relationships needed, or are those two things the "same thing" (a.k.a. combine the boxes).
once you're to here...voila - data model. Like he mentioned - once you actually put it to paper, and visualize - chances are good you will come up with a half decent data model first time out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply