PK/FK Same Field in 1:1 Relationship?

[CPGDeveloper]

Hello~

DB Design question -- say I have a primary table, tCompanies, where the primary key is CompanyID.

And I have another table, say tCompanyMetrics, where every field in that table has a one to one relationship with CompanyID.

In tCompanyMetrics, do I need both a primary key field and a foreign key field? Cannot I put a field in tCompanyMetrics also called CompanyID and have that field as both the primary key and foreign key.

This stackoverflow article seems to suggest this is fine -

Is it fine to have foreign key as primary key?.

But before I build this, I wanted to see if there is another school of thought about this or if I'm missing something here. Thanks!

 

[Micron]

AFAIC you'd never have a 1:1 relationship between tables as you've described as there is no point. Perhaps your terminology isn't correct for what you need to know? There can only be 1 metric per company and the company record in the company table is never repeated as it should not be? That's what you're suggesting. In that case you could end up with a major re-design if the business model changed and all of a sudden you have 2 or more possible metrics per company. Even if you can only have 1 at a time, if the value of that 1 can change then with a 1 to 1 you will have work to archive or you will have to be content to lose historical data. It is rare that a 1 to 1 relationship makes any sense but I'm not saying never.

fk's are supposed to be from the many side of a relationship, and yes, you can have the same field names in different tables and no, you should not. It's common to add fk or _fk to the foreign field name where the prefix is the pk field name from the parent (1 side of the 1 to many relationship). Some add _PK suffix to the primary key (PK) field name. So f'rinstance
tblCustomer.CustomerID --> tblMetrics.CustomerID_fk

If you are not talking about a constraint as I initially thought, then perhaps you're saying that the metrics table will likely never have 2 or more records for the same company but IF it should be necessary then there is no constraint. In that case you have a 1 to many by design but a 1 to 1 in practical use. They are not the same thing.

 

[CPGDeveloper]

Hi, thanks for your thoughtful reply. Apologies for not being clearer.

Let me give a bit more background. We are redesigning our database, which currently uses a primary table that has over 100+ fields. Each of the 100 fields has a one to one relationship with the primary key of this table (I swear!). We have other data that has a one to many relationship with this table, and of course we have put that data in separate tables with it's own primary key and foreign key linking back to the primary table.

As the database grows, I have been advised to break up this large 100+ field table. So let's call this table tCompany with a primary key of 'CompanyID'. In a scenario as this, I was advised to break up the table by data types. So all the Currency fields, I would put in a separate table and call it, say, tCompanyCurrency and all the large text fields in a table called tCompanyMemo.

My question is in joining tCompany and tCompanyCurrency (or tCompany and tCompanyMemo) do I need both a primary key and a foreign key in tCompanyCurrency (or tCompanyMemo)? Thanks again.

 

[Micron]

do I need both a primary key and a foreign key in tCompanyCurrency (or tCompanyMemo)?

It is highly advisable, otherwise you have no unique identifier for any particular record unless the PK is meaningful data, which many say it should not be.

So by 1 to 1 you mean the table might be normalized. If you have repeating fields, it's not. An example of that would be Payment1 | Payment2 | Payment3...
which might explain why you have 100 fields. I've seen it before and it was correct, but IMO it's not always correct design.

A question might be (assuming the table is properly normalized) what do you expect to gain from breaking it up, performance? Perhaps if you have millions of records and most of the fields are almost always static, but you will introduce a performance hit by creating joins that you don't have now. So if many and frequent execution of queries on what was singular data becomes the norm you may find that there is zero payback in terms of performance. I leave it up to you do figure out if the table has too many fields because it's not properly normalized.

 

[CPGDeveloper]

Thanks again -- I appreciate your response. Yes it seems to makes sense to also have a Primary Key regardless.

As far as I can tell, we have normalized as much as we can -- (we don't have things like Payment1, Payment2, etc.) -- another db consultant and I have gone through every field, and essentially we have these 100+ fields that are truly one to one with our primary key. I will comb through everything again however to see if there is something I'm missing as far as normalization.

To your other point, however, I agree with you -- I don't really see why we need to break it up -- it's working just fine as it is. And breaking it up will force us to re-write all our reports and other related scripts. But every other data person I've talked to or every board I've posted this issue has insisted that a table with that many fields will eventually cause issues. Nobody can seem to tell me why exactly -- It does 'feel' wrong to have a table with so many fields, but that's certainly not a good enough reason.

Anyway thanks again -- if I do go this route in breaking up this table, I will have both a primary key and a foreign key.

 

[Micron]

FWIW, I don't agree. Someone wiser than me once said, "if it ain't broke..."
If your tables are under performing, a version of sql server might be the way to go. I have worked with sql server tables that had I many fields (can't recall how many, but several of them were in the 100 range) so the issue can't be all that bad. Good luck!

 

[CPGDeveloper]

Thanks Micron -- we're actually on Azure SQL Server Back End/Access Front End. Internally we've been having this discussion whether to break up this table or not. I'd rather not, but we'll see.

 

[xenou]

I think it would work both ways (or all of the ways floated so far) ... CompanyID to CompanyID (primary key to primary key, or CompanyID to CompanyID (primary key to foreign key).

I appreciate that you are saying this is a one to one relationship but in the world of metrics I would prefer the foreign key approach as metrics are statistics and statistics love to multiply ... even the simple fact that metrics change over time suggests you will have different sets of metrics representing different periods (Q1, Q2, FY2020, FY2021 etc.) when the metrics were calculated.

Nonetheless, since I don't actually know what kind of data your metrics really represent, I leave it to you to judge whether a 1:n relationship may ever in fact be feasible or if it would be a good idea to consider your relationships with the possibility of such a future change in mind. For what it's worth, I think that if you joined CompanyID to CompanyID (primary key to primary key), then you are enforcing the 1:1 relationship. If you had a primary key to foreign key relationship, I think you could still enforce the 1:1 relationship by adding a unique constraint to the table so that the CompanyID could only exist once in the table (I'll assume 1:1 admits 1:0 as well, for simplicity).