database model reading and digesting

i am not good with table relationships on primary foreign key and 1-n and n-1 etc relationships, data model diagram reading esp when there are 100 tables connecting one other? how do i go about digesting those diagrams.
shoe i take printout and using a pencil mark physically which table is talking to which super table and sub table.

please advise any good links, resources, training, free video tutorials on this.
this concept is same across any kind of database right?
LVL 7
gudii9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I'm not sure I understand what you are asking.  If you have a properly created data model you should be able to see not only what tables/entities (physical/logical) reference each other and what type of relationship it is (1-n and n-1 etc).

I'm not sure what you would be wanting to mark with a pencil?

>>this concept is same across any kind of database right?

Yes, logical and physical database modeling concepts should be the same.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gudii9Author Commented:
I'm not sure I understand what you are asking.  If you have a properly created data model you should be able to see not only what tables/entities (physical/logical) reference each other and what type of relationship it is (1-n and n-1 etc).

if go to different companies say on consulting work every where they have different data models for the project with all tables in those diagrams
for example
https://www.google.com/search?q=datamodel+diagrams&safe=active&rlz=1C1GGRV_enUS765US765&source=lnms&tbm=isch&sa=X&ved=0ahUKEwi1hsSt46LXAhUG6SYKHX85AMAQ_AUICigB&biw=1272&bih=611#imgrc=dx8MqL_0BD_BfM:
https://www.google.com/search?q=datamodel+diagrams&safe=active&rlz=1C1GGRV_enUS765US765&source=lnms&tbm=isch&sa=X&ved=0ahUKEwi1hsSt46LXAhUG6SYKHX85AMAQ_AUICigB&biw=1272&bih=611#imgrc=7ANkCzFyQr76xM:
how to read and understand them
0
slightwv (䄆 Netminder) Commented:
If you look at those two:  One is a physical model and one is a logical model.

I cannot recommend any specific "go to" source to learn everything you need.

I would probably start with Wiki and then Google around once I start learning the terminology.

https://en.wikipedia.org/wiki/Data_modeling
https://en.wikipedia.org/wiki/Data_model

For example look at the different types of visualizations you can use when creating models:
Bachman diagrams
Barker's notation
Chen's Notation
Data Vault Modeling
Extended Backus–Naur form
IDEF1X
Object-relational mapping
Object-Role Modeling
Relational Model
Relational Model/Tasmania

Once you learn those (and any others you may find), you'll know how to read the models you come across.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

gudii9Author Commented:
any single link or resource or book to learn all of above at reasonably good level?
0
slightwv (䄆 Netminder) Commented:
Again:
I cannot recommend any specific "go to" source to learn everything you need.
0
gudii9Author Commented:
i found one pdf online
https://www.sparxsystems.com.au/resources/user-guides/model-domains/database-models.pdf

is this enough to learn those things?
0
slightwv (䄆 Netminder) Commented:
I cannot say.  It is too subjective.

Even if I think it is good, you might read it and still not understand one or more of the concepts explained in it.

I don't know your current skillset and I'm not going to read 170 pages and give a review on how complete I feel it is.
0
Olaf DoschkeSoftware DeveloperCommented:
There are several different ways to draw these diagrams, but even when you first don't understand what type of a relation you have at hand, it boils down to three situations most of the time:

1. A table has a foreign key (OrderDetail.OrderID for example point to the Order - super table). At the same time, this means the order detail table is the sub table of the orders.
2. n:m relationships are physically modeled by a table in between two tables having two foreign keys to make that relationship. Such tables may contain further data related to the relationship, but often enough just are these two IDs, for example as a person may have several addresses and several persons can live at an address you may simply find a residents table with PersonID, AddressID, which you can view from the point of view of a specific address or a specific person.
3. Rare cases of 1:1 relationships, which may be used to avoid a very wide table with many nullable columns. Instead, you opt for a primary key equal to that of the 1:1 related table or a foreign key with a non-duplicate index to make clear, which of the 1:1 related tables still is the main table.

Since 3 is rare, you might even not find such relations.

These 3 rules simply follow from the technical detail you store one primary key value as a foreign key in one record. Since foreign keys typically don't have the constraint to be unique like primary keys (as that only enables 1;1 relationships), many records of the table having the foreign key can point to the same record of another table (where it is the primary key). You have (typically) many order details in an order, you have many products in the same product category, etc. So the foreign key table is the many side table the subtable.

This is unambiguous about the 1 and n side of relations. But this technical relation doesn't tell you about the natural meaning of the relationship anyway. Most often it is obvious, as in orders and order details, or products and product categories, as that is well known. But then notice how you value these technically same relationships differently:

Of course, you'd see order details as sub-table of the main order table. But do you see products as sub table of product categories? You'll judge products as the main data and a product category merely being an attribute of a product, quite unimportant. The nature of these relationships differs in the amount of data. Order details always are of a certain order and orders grow in time together with order details, of course not as fast, but every new order also is a new record in orders, while product categories are stem data you may even already think about when deciding your business, Once you decided for product categories to sell, you may add new products, but only, if they match your business. And if a company decides to concentrate on certain product categories, it may become very important to consolidate products into a cleaned up list of product categories or even remove them from the range of sold items, so it also is a rule of thumb regardless of how you see at tables the 1 side always is the parent table and the n side the child table.

This meaning of the relationship, which also is important to understand a database is not stored in the database, you'll find that meaning in the code and documentation (hopefully), mostly in the way data is queried and presented in the application frontend and of course in the data itself.

Bye, Olaf.
0
Scott PletcherSenior DBACommented:
Large models can be difficult to understand, esp. in a short time.

I've found the best way to start is to ask someone who is knowledgeable about the model for the most important entities / tables (for logical / physical model, respectively) in the model.  Next, look at the ents / tabs with direct relationships to them.  If any of those are "master" ents / tabs, look at their direct relationships.  Otherwise, ignore the rest at the start, as they are not that significant anyway.

For example, when looking at SQL's metadata model, you'd start at sys.databases and sys.objects and work your way out from there.  And that alone would give you a quick but reasonably effective overview of that model.

Good luck!  And always remember that logical models and physical models are fundamentally different and even serve different purposes, so don't mix the two or assume that one can replace the other.
0
gudii9Author Commented:
1. A table has a foreign key (OrderDetail.OrderID for example point to the Order - super table). At the same time, this means the order detail table is the sub table of the orders.

here OrderDetail is sub and Order is super table?

whichever has foriegn key for the relationship always sub and other table which has primary key is super table?
please advise
0
Scott PletcherSenior DBACommented:
Orders and OrderDetails aren't actually a super table and subtable, rather they're a parent and child table (or referenced table and referencing table).

A subtable is a table that is a specific subtype of a super table.  The subtable will have all the columns of the super table, plus any relevant to its specific subtype.  For example, in SQL Server itself, sys.objects is a super table and sys.tables is a subtable.
0
gudii9Author Commented:

here OrderDetail is child and Order is parent table?

whichever has foreign key for the relationship always child and other table which has primary key is parent table?
i corrected as above. does it look correct now?
0
slightwv (䄆 Netminder) Commented:
No.  In the design world ALL tables have a primary key.  Not ALL tables have children.

Consider a STATE table:
state_code char(2),
state_name varchar2(200)

Primary key is state_code.  It has no children.  It has a LOT of parents that needs a state_code foreign key.
0
gudii9Author Commented:
No.  In the design world ALL tables have a primary key.
i can have a simple table  say xyz_table without primary key right?
Consider a STATE table:
state_code char(2),
state_name varchar2(200)

Primary key is state_code.  It has no children.  It has a LOT of parents that needs a state_code foreign key

not clear. are you saying state_code is primary key or foreign key?

what is parent and what is child. can you please elaborate

lets say there is country table and also there is county table

is county is child of state table where as country is parent of STATE table?
please advise
0
slightwv (䄆 Netminder) Commented:
>>i can have a simple table  say xyz_table without primary key right?

You can do whatever you want.  You "can" probably make a database that stores everything in a single table.  It isn't normalized and won't be efficient but you "can".  Nothing stops you from doing it.

If you follow the rules of normalization every table will have a primary key.

>>is county is child of state table where as country is parent of STATE table

Does the country, county or state share columns?  Probably not so there is no parent/child relationship between them.

What defines a parent/child relationship is the column(s) the two tables have in common that, well, relates them.

An Address table likely has a state_code and country_code column so it would be a parent to the state and country table.  If the address table has a county column, then it is also a parent to the county table.

Lookup tables like state, county, country, etc... normally don't have any children.
0
Scott PletcherSenior DBACommented:
I'd say don't worry about a formal "primary key" per say (after all, real-world things sometimes aren't as neat as mathematical models.).  But whenever possible have a unique clustered index.  For example, on the state codes table, a unique clustered index on state_code would work just fine.
0
gudii9Author Commented:
An Address table likely has a state_code and country_code column so it would be a parent to the state and country table.  If the address table has a county column, then it is also a parent to the county table.

above is clear as state child table depends on Address parent table  column state_code similarly country, county

Lookup tables like state, county, country, etc... normally don't have any children.
what is meaning of lookup table why they do not have children?
0
slightwv (䄆 Netminder) Commented:
>>what is meaning of lookup table

They allow you to store a shorter name/id instead of a longer term.  It saves space and also allows you to change the main term easily since you don't have to change the values in ALL the parent tables.

In the state/address table, you only need to store the code in the address table and not the actual state name.

>>why they do not have children?

They are almost always at the bottom of the relational design.  What would be the child of a state table?
0
Scott PletcherSenior DBACommented:
What would be the child of a state table?

A county table or a city table, if you required such data.
0
slightwv (䄆 Netminder) Commented:
Wouldn't a county or city table would be a parent of state?  What would the design of the state table look like if it had county as a child?
0
Scott PletcherSenior DBACommented:
The state table design doesn't change, you'd just have the state key in the county table.  I can't imagine how the state could be a child of the city.
0
slightwv (䄆 Netminder) Commented:
>>you'd just have the state key in the county table.

I think I flipped my naming of a parent and a child.  I haven't thought in those terms in many years.
0
gudii9Author Commented:
I think I flipped my naming of a parent and a child

i am confused. can you please elaborate without flipping
0
Scott PletcherSenior DBACommented:
Certain tables have a natural hierarchy, where a row in a "lower" or "child" table cannot exist without a row in the "parent" table.

For example, OrderItems is a child to the Orders table: an item on an order cannot exist without an order.

A zip code is not necessary useful without a country (the country may be implied/assumed, as whatever country you are in, but it's still needed to give the zip code full meaning).  In that sense, a zip code table would be child to the country table.

In business modeling, a child will typically have only one parent, as in OrderItems --> Order.  In that case, the parent key is placed in the child table itself.  The OrderItems table would have a column named "OrderId", for example.

In some modeling, a child may have more than one parent.  For example, an actual child and his/her parent(s).  In that case, a separate table is used to store matching keys.  For example: table Parents_Children ( ChildId, ParentId ), ChildId = 1 might have two rows, ParentId = 1 for Mom and ParentId = 2 for Dad.  There would be a separate "Parents" table and a "Children" table.
0
gudii9Author Commented:
In some modeling, a child may have more than one parent.  For example, an actual child and his/her parent(s).  In that case, a separate table is used to store matching keys.  For example: table Parents_Children ( ChildId, ParentId ), ChildId = 1 might have two rows, ParentId = 1 for Mom and ParentId = 2 for Dad.  There would be a separate "Parents" table and a "Children" table.
first row ParentId = 1 for Mom then parentid is null for dad?
second row ParentId = 2 for Dad then parentid is null for Mom?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.