Search code examples
sqlsql-serverdatabasedatabase-designstar-schema

Primary key is also a foreign key in a star schema. Good or bad?

I am creating a database following star schema. This is my schema: enter image description here

The question is: is it good or bad practice that my side tables don't have an ID column but uses the same column ApsilankymoID as PK, and FK?

Relationship with 1Apsilankymas and other tables is 1:0..1.

Thanks

Solution

  • That's called vertical partitioning. Let's say you have one table with mostly small data types (INT, DATE, etc.) and one column that's a big data type like NVARCHAR(8000). By moving that large data type into its own table, sharing the same PK as the other table, it can reduce IO on the server; especially if you don't use the large field that often. If it's not that much data in the table, you probably won't get the bang for your buck, but if it's a lot of data, it can help a lot. Here's a site with more info. There's other intricacies that you should read about. As far as good for a star schema... not sure. I'm sure someone else will have a good answer there.

    https://www.sqlshack.com/database-table-partitioning-sql-server/