Db design
I have a problem with DB design. In this ER diagram, i am trying to build a db that allows users to place product under which other users can request a feature. This feature can then have comments to discuss. The problem is that composite key of a comment is UserID, productId, featureId, authorId, Date (when the comment was written). As you can see it is way too long. Is there a better way to design this? Thanks.
108 Replies
Just don't use a composite key?
Idea then break. It then woul've meant that comment can exist by itself
Use
(AuthorId, FeatureId) then, since a comment is left by a user on a featureYou mean to add Feature entity featureId?
No?
I mean this
Again feature depends on a product. If i were to add that it means that feature doesn't need product to be identified uniquely
You can have the composite key of the feature be
(Id, ProductId) still
Though, I usually just use sequential IDs and simple FKs, composite keys only when I need to enforce that a given pair of values needs to stay unique
So you're operating on levels of database normalization or whatever that not even my college professors taught us about lmao
Maybe people in the #database channel would be more familiar with such... unique conceptsYes, i am trying to apply normalizations to construct this Database
is this a repost? i swear i saw this question earlier today
i did it yesterday
i chnaged that diagram
removed some unneccessary keys but the problem stays the same
why do you need a composite key at all? that will complicate retrieving comments
just have a normal PK and non-nullable foreign keys to the entities that own the comments
How else would you identify comment uniquely?
the same way most tables identify things uniquely
By a unique primary key
Yes but there is a rule that dictates that all columns have to be identified by primary key
All columns?
Whuh?
of an entity
i think there's some terminology confusion
Columns are identified by their name
Columns don't have keys
why do you think you need to make all this information part of the comment's key
the key is just an identifier, you can have columns for a comment that aren't part of any key
A relation that is in First Normal Form and every non-primary-key attribute is fully functionally dependent on the primary key, then the relation is in Second Normal Form (2NF).
based on your diagram you can use a normal integer PK and have FKs to the feature and author
Sure but when retrieving that you would've went for that FK of a feature where you're would've went for it's FK which is prodID
huh?
your diagram clearly shows a one to many relationship between a feature and comments
yes
the product/feature relationship is not relevant here
are not you suppose to identify uniquely that feature?
what if that feature was written several times on several products
It would have a unique ID, wouldn't it?
then you would use the key of the feature the comment is for?
that's the whole reason you give unique items unique IDs
In order to find Comment you need to uniqely identify feature.
In order for you to uniqely identify feature, you need to find product and the user that wrote that product.
why would you need to do that
the unique identifier for a feature should be a single unique value, like a number
In order to find a comment you need that comment's ID
Sure and you then apply FK?
it seems like you're trying to embed information in the row that you should be connecting with joins in your queries
to see where it was written?
Wym "apply FK"?
How do you find where that comment belongs?
if you want to get all the comments for a feature, just select the comments where the feature ID matches the feature
You use an FK, yes
The comment would have an FK to feature
Sure now stay with me
That FK is connected to Feature
right?
Yeah
yes
now you have to findout where that feature is written
on what product
then you use the FK between the feature and product
so now you have to find out product rifght?
Sure, the feature has FK to product
Nothing simpler
sure
and then to find product you need to find user
who wrote that
again, the FK
yes,
to get from a comment to a user you'd just join across all 4 tabes on the relevant FKs
so in order for you to uniquely idnetify comment you need to go to all of these tables.
right?
No
You need a unique primary key
That's it
An entity is not defined by the entire chain of relationships it has
It would be nonsensical
Sure but that unique primary key doesn't know what feature it references. Feature FK idnetifys that comment
it doesn't need to
Primary key is not there to reference anything
its only purpose is to uniquely identify the comment
not its relationships
It's there to be referenced
you identify relationships using FKs
But that what composite keys are used for
which are columns that store the primary keys of the other entities being referenced
no
there is no reason to use a composite key for any of this
Okay, lets say i add comment ID
Composite keys are rarely used either for a natural key, or to ensure that a combination of
n properties is uniqueTo uniquely identify it.
They are not and should not be your first choice for a unique identifier
But if i do so and have COmmentId in COmment entity. And i have to query specific comment under specific feature under specific product
then you use joins
then why do i need commentId?
To identify the comment...?
...to uniquely identify each comment
if all these FK are identifying
they are not identifying
the FKs tell you relationships
not identity
FKs are referencing
PKs are identifying
You are uniquely identified by the name
Dachi
It does not reference the chair you sit on
Your chair can have a plaque that says Dachi's Chair
It references its ownerpractically every table in your database should have an "ID" column that serves as the primary key, and it can be a simple integer
But why can't those FK be PK?
then you use those IDs to create FKs and create relationships between different tables
because they aren't for the same purpose
that's like saying you are not dachi, you are "dachi's mom's child"
Why would they be PKs?
But i will then have to overload these tables
?
what
if you have no PK what will the FK reference
to use less unused PK. I will use FK as PK
unused?
Yes cuz for me to search comment i will have to search it by FKs
huh?
"Why is my fork leaking?" you ask, "Forks are made for soup!"
"Use a spoon," we say' "forks are not great for eating soup."
"No but forks is how you eat soup! How else will you eat the vegetables?"
i don't get why you're overcomplicating what's really a basic relational database concept
I am not. It's just the way it will then be graded
this is why you need PKs
it has to follow normalizations
the feature FK on a comment needs to reference the PK of the feature
I have never seen a normalization that would require the PK to be a composite key referencing all the relationships an entity ever had
^
that sounds more denormalized than anything
why should a comment need to know about the entire chain of relationships when its only direct relationship is with a feature?
damn i gotta think
What Pk of exam should be?
it is composite key of StudID and exam part
a number that distinguishes it from all the other exams
no
stop thinking that PKs need to be composite keys of related entities
No this is the answer
literally, all a PK is is a value that tells different rows in the same table apart
pk of exam is composite key of studId and exam_part
Like that you say destroys the idea of weak entity
you didn't mention anything about a "weak entity" until now
that beacause i din;t want to talk using terminologies
...
that is critical information
you asked us how to do one thing, we told you the right way to do it, you argued that we were wrong, then you decided to tell us you wanted to do something else entirely
The relationship depicts that it is a weak entity
ER diagram itself represents that
that's why inside square there is another square to depicts it's weak
that's why i did't use terminology
then no there is no better way to do what you want
other than modeling the data differently
Sure, i'll think about it a little more and then i guess will chnage it
thanks for the time