1

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

Deivydas Voroneckis
  • 1,973
  • 3
  • 19
  • 40
  • 1
    I suppose it is fine as long as that relationship will never change from a 1 to 1, but it could cause all kinds of issues if the relationship ever needs to change. – Ryan Wilson May 10 '18 at 13:10
  • 1
    If all of the relationships are 1:1, why split them out into other tables at all? That's not really what I'd call a star schema. – Tab Alleman May 10 '18 at 13:17
  • 1
    If the entities you model are [weak](https://en.wikipedia.org/wiki/Weak_entity), including the FK in the PK is the right way to do it. If it's a 1:1 relationship the PK and FK are the same, as it seems to be the case in your situation. – sticky bit May 10 '18 at 13:47
  • @TabAlleman thanks for comment, I was a bit unclear about relationships, actually it is 1:0..1. Main table is mandatory. Do you think in this case it could be defined as a star schema? – Deivydas Voroneckis May 11 '18 at 10:52

1 Answers1

2

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/

Utrolig
  • 251
  • 2
  • 13