Relationship between a history tracking table and a non-history tracking table?

Hello. First off, let me say that data modeling is fairly new to me so please be gentle with me. I'm currently working a relational model with only 2 tables. The first table(table A) holds information about other tables in a database such as the table name, a date, a snapshot number, and a row count of that table on that date. The snapshot number is a time series indicator that gets assigned to a new snapshot when it's taken. For example, I take a snapshot of that particular table today. That gets assigned a snapshot number of 1. Tomorrow, I take another snapshot of that same table which gets a snapshot number of 2. This will allow me to find the growth of a table based on row counts using any of those dates or snapshot numbers. In short, this is a history tracking table with accumulating snapshots. Now, the other table(table B) is along them same lines but it contains all the column information. It contains the table name, column name, a date and some other very miscellaneous information about the columns. This table(table B) will only contain information about the columns according to the most recent snapshot. In short, there's no history tracking on this table. Now, I know that logically a table needs to exist before a column can exist. Therefore, to me at least, this states that table A has a relationship with table B. The relationship going from table A to table B would be 1 to 1 or many. The issue comes with the relationship. I'm using ER/Studio to do modeling. When I create this identifying mandatory relationship between these two tables, it is automatically bringing over the snapshot number from table A as it's part of the primary key on table A. This is not really necessary to have in table B as a foreign key since it's only relevant to the most recent snapshot. The primary key on table A is snapshot number and table name. I want the primary key for table B to only be table name and column name and not to have the snapshot number as part of it. The questions become: should I even be mixing a history tracking table and non-history tracking table in the same model? Should there even be a physical relationship between table A and table B? I believe there should be, but maybe I'm wrong. For joining purposes, I'd like to join on table name from both tables, A and B. Because of this I believe that I need table name to be a primary key on table A and a foreign key on table B. Maybe I have this all modeled incorrectly. Any suggestions you can lend a new data modeler for this issue would be greatly appreciated.

Here is a quick picture of my current logical model. This will most likely help give more context for understanding the issue. http://postimg.org/image/apv434vb7/

Thank you in advance.

There are 'cheats' in ERWin that may apply to ER/Studio.  You can declare an alternate key with the columns you need and use the alternate key as a role in the relationship.  It will only propagate the columns in the alternate key.

But, if what you are attempting to do is create a 3NF model, you need a 3rd entity, a table table, which the other two tables would reference.

You can't use the FK either way you define it. Once the "real" row is deleted, there won't be a parent for the history table.

Ngalemmo - thank you. I'll check into that.

BoxesandLines - I'm confused. The HR_TABLE_INFO table is a table full of information about the HR tables in a database. The HR_COLUMN_INFO table is a table with the column information related to those given tables. At a bare minimum, TABLE_NAME should be the PK in the HR_TABLE_INFO table and as a FK in HR_COLUMN_INFO. Can you please elaborate a little better? Sorry, this is all newer to me.

No worries, I was confused as well. Let's start at the top. Facts are things you count. Dimensions are things that describe facts. Looking at your model, here's what I see:

Facts
Table Row Count

Dimension Columns
Everything else

Try redesigning your model with this in mind and report back. We can address some grain issues once the model is in better shape.

BoxesAndLines,

Thank you for the reply. I understand the concepts of facts and dimensions. I should have specified this earlier, but this model will only be 3NF relational and not dimensional. I have explored some options in ER/Studio and think I may have a workaround for now. I'll report back here once I dive into this a little deeper. Thank you for the assistance this far.

Ahhh, in that case you might want to review the normalization rules. Your current design is in 1NF.