December 16, 2007 at 2:52 pm
(If this is a duplicate, my apologies I was unable to find any other comments/questions like this one)
Typically when I join two or more tables to a third table (one-to-many) there are two join tables. For example say there are two primary tables; a Car table and a Person table. If I want to have pictures for both, there isn't nessecarily a reason to create a separate picture table for both primary tables (I suppose anyone can come up with a reason, but thats out of the scope of my question). So I create a picture table and two join tables (all using Seeded Ints for Keys). Now I have five tables, I create my primary keys, my indexes, my relationships as normal and everything is good.
Now I design it the exactly same way, but without Join Tables, and using GUIDs instead of Ints. What replaces the Join tables is a new Column in the Picture tabled called ParentUI. Since the GUIDs are (theoretically) unique, I can join this single column to both Car and Person primary key columns.
I found this design in a database that a friend of mine is working on (he isn't the DBA), so I'm geniunely curious about other peoples thoughts on this.
December 16, 2007 at 5:21 pm
I could see how this helps and it might be easier, but it depends on your data. With the two join tables, the advantage is a picture can be used in both the Car and Person tables. In your new design, you'd need to include that picture twice.
Perhaps that doesn't apply in your problem space, and if not, then GUIDs could definitely reduce the joins required.
December 17, 2007 at 8:08 am
I'm not sure how big the database is going to be but storing guids does take up much space relative to an int. This is something i think you should keep in mind while you work on a design for a database 🙂
December 17, 2007 at 8:22 am
While it might be nice to do that, you've now destroyed any abilty to enforce relational integrity, so by "saving" yourself one table, you've now DEnormalized (since your FK no longer points to a single parent entity). You've also destroyed any ability to use many to many relations (should you need it)
There's been a huge thread on consolidating all of your reference data into one table on here, and while I wouldn't encourage you to read it (it's like several hundred rather heated posts long), the bottom line I pulled out of it is - the headaches you engender by doing that far far outweigh saving yourself one line of display in SSMS.
Now - keep in mind that there are exceptions to all rules, but this is a dangerous one, and one likely to wreck most models.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 17, 2007 at 8:28 am
Good points, though I guess you could do what many people do and put in 2 columns in pictures and join one to either table. You'd be wasting a column, probably a NULL value, in each row, but you'd still have less joins.
I'm not a big fan of this either. I don't like GUIDs as they're hard for humans to work with and lead to potential mistakes when you're troubleshooting or trying to figure out where things are linked.
If you want to make things simpler, include the joins in a view and use that for your queries.
December 17, 2007 at 10:45 am
guids are really bad ideas as keys and you still need constraints to guarantee uniqueness. see my paper about index size which compars a guid to an int on a large table http://grumpyolddba.co.uk/sql2005/working%20with%20indexes%204.mht
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
December 18, 2007 at 2:50 am
The GUID is not a magic bullet. You can do your database design just as well with other data types.
I do recommend you read Colin's post. He is seldom wrong.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 18, 2007 at 7:16 am
You could start your CarID values at 1,000,000,001 and have room for one billion non-overlapping people and cars. Or seed one of the tables with -2147483647 and have over two billion of each. Therefore if you are determined to destroy relational integrity you don't have to resort to GUIDs to do it.
GUIDs are the right answer in some cases, but they would be my last resort. In most cases and 8-byte key is bigger than needed, and they have some strange behaviors. We had an issue once with a "SELECT COUNT(*) FROM tbl WITH(NOLOCK)" query, where you never get the right answer on a table with a GUID clustered primary key.
December 20, 2007 at 9:42 pm
I wasn't really trying to start and GUID vs Int thread, I've read this to death. I was just interested in this design I found and was looking for comments on the design. Thanks for all the input!
So what kind of person does this? Inexperienced and/or Lazy?
December 21, 2007 at 7:38 am
I've known developers who prefer GUIDs because they can create an object in code anywhere, create the GUID primary key, then use the GIUD to create related objects, and finally send the whole package off to the database in one call. They usually aren't thinking about the overhead of storing GUIDs in the database.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply