Tweeted twitter.com/StackDBAs/status/901062110310014977
4 replaced http://stackoverflow.com/ with https://stackoverflow.com/
source | link

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4GUID v4

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4

3 Modified title; added relevant tag; made some rewordings; changed formatting; incorporated comment interaction.
source | link

Multiple Table Relationship How to model an entity type that can have different sets of attributes?

I'mI’m having some trouble in recreating a database with a 1one-to-many (1:NM) relationship between UsersUsers and ItemsItems.

This is pretty straightforward, yes, yes; however, each ItemItem belongs to a certain CategoryCategory (e.g., a Car, a Boat or a Plane), and each CategoryCategory has a variableparticular number of attributes. E, e.g.:

Car structure:

Cars:  +----+--------------+--------------+
| PK | Attribute #1 | Attribute #2
Boats: |
+----+--------------+--------------+

Boat structure:

+----+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 |
Planes:+----+--------------+--------------+--------------+

Plane structure:

+----+--------------+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 |
+----+--------------+--------------+--------------+--------------+

Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each CategoryCategory, so I would avoid several NULLs and thus making a better use of the indexing.

Although it looked great at first, I couldn'tcouldn’t find a way to create the relationship between the ItemsItems and the CategoriesCategories through the database because, at least in my modest experience as a database administrator, when creating Foreign Keys, I inform explicitly a database, the table name and column.

In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all ItemsItems a userUser may have with one query.

I could hardcode dynamic queries with the server-side language, but I feel this is wrong and not very optimal.

Additional informations fromAdditional information

These are my responses to MDCCL comments:

(1)1. How many Item CategoriesItem Categories of interest are there in your business context, three (i.e., CarsCars, BoatsBoats and PlanesPlanes) or more?

In fact, it'sit’s very simple.: There are only 5 Categoriesfive Categories in total.

(2)2. Will the same ItemItem always belong to the same User (that is, once a given ItemItem has been "assigned"“assigned” to a certain UserUser it cannot be changed)?

(3)3. Are there attributes that are shared by some or all of the CategoriesCategories?

Not shared but, from memory, I can tell that at least 3three attributes are present in all categoriesCategories.

(4)4. Is there a chance that the cardinality of the relationship between UserUser and ItemItem is many-to-many (M:N) instead of one-to-many (1:M)? For example, in the case of following business rules: A User owns zero-one-or-many Items and and An Item is owned by one-to-many Users

No, because ItemsItems would describe a physical object. UsersUsers will have a virtual copy of them, each one identified by a unique GUID v4

5. Regarding your following response to one of the question comments:

“In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.”

It seems that you are planning to keep track of the item ownership evolution, so to speak. In this way, which attributes would you like to store about such phenomenon? Only the modification of the attribute that indicates the specific User who is the Owner of a specific Item?

No, not really. The ownership may change, but I don’t need to keep track of the previous Owner.

Multiple Table Relationship

I'm having some trouble in recreating a database with a 1:N relationship between Users and Items.

This is pretty straightforward, yes, however each Item belongs to a Category and each Category has a variable number of attributes. E.g:

Cars:   PK | Attribute #1 | Attribute #2
Boats:  PK | Attribute #1 | Attribute #2 | Attribute #3
Planes: PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4

Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.

Although it looked great at first, I couldn't find a way to create the relationship between the Items and the Categories through the database because, at least in my modest database administrator, when creating Foreign Keys, I inform explicitly a database, the table name and column.

In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a user may have with one query.

I could hardcode dynamic queries with the server-side language, but I feel this wrong and not very optimal.

Additional informations from comments:

(1) How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?

In fact, it's very simple. There are only 5 Categories in total

(2) Will the same Item always belong to the same User (that is, once a given Item has been "assigned" to a certain User it cannot be changed)?

(3) Are there attributes that are shared by some or all of the Categories?

Not shared but, from memory, I can tell that at least 3 attributes are present in all categories.

(4) Is there a chance that the cardinality of the relationship between User and Item is many-to-many instead of one-to-many? For example, in the case of following business rules: A User owns zero-one-or-many Items and An Item is owned by one-to-many Users

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4

How to model an entity type that can have different sets of attributes?

I’m having some trouble in recreating a database with a one-to-many (1:M) relationship between Users and Items.

This is pretty straightforward, yes; however, each Item belongs to a certain Category (e.g., a Car, a Boat or a Plane), and each Category has a particular number of attributes, e.g.:

Car structure:

+----+--------------+--------------+
| PK | Attribute #1 | Attribute #2 |
+----+--------------+--------------+

Boat structure:

+----+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 |
+----+--------------+--------------+--------------+

Plane structure:

+----+--------------+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 |
+----+--------------+--------------+--------------+--------------+

Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.

Although it looked great at first, I couldn’t find a way to create the relationship between the Items and the Categories through the database because, at least in my modest experience as a database administrator, when creating Foreign Keys, I inform explicitly a database the table name and column.

In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a User may have with one query.

I could hardcode dynamic queries with the server-side language, but I feel this is wrong and not very optimal.

Additional information

These are my responses to MDCCL comments:

1. How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?

In fact, it’s very simple: There are only five Categories in total.

2. Will the same Item always belong to the same User (that is, once a given Item has been “assigned” to a certain User it cannot be changed)?

3. Are there attributes that are shared by some or all of the Categories?

Not shared but, from memory, I can tell that at least three attributes are present in all Categories.

4. Is there a chance that the cardinality of the relationship between User and Item is many-to-many (M:N) instead of one-to-many (1:M)? For example, in the case of following business rules: A User owns zero-one-or-many Items and An Item is owned by one-to-many Users

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4

5. Regarding your following response to one of the question comments:

“In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.”

It seems that you are planning to keep track of the item ownership evolution, so to speak. In this way, which attributes would you like to store about such phenomenon? Only the modification of the attribute that indicates the specific User who is the Owner of a specific Item?

No, not really. The ownership may change, but I don’t need to keep track of the previous Owner.

2 Additional informations from questions in comment
source | link

I'm having some trouble in recreating a database with a 1:N relationship between Users and Items.

This is pretty straightforward, yes, however each Item belongs to a Category and each Category has a variable number of attributes. E.g:

Cars:   PK | Attribute #1 | Attribute #2
Boats:  PK | Attribute #1 | Attribute #2 | Attribute #3
Planes: PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4

Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.

Although it looked great at first, I couldn't find a way to create the relationship between the Items and the Categories through the database because, at least in my modest database administrator, when creating Foreign Keys, I inform explicitly a database, the table name and column.

In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a user may have with one query.

I could hardcode dynamic queries with the server-side language, but I feel this wrong and not very optimal.

Additional informations from comments:

(1) How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?

In fact, it's very simple. There are only 5 Categories in total

(2) Will the same Item always belong to the same User (that is, once a given Item has been "assigned" to a certain User it cannot be changed)?

No, they could change. In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.

(3) Are there attributes that are shared by some or all of the Categories?

Not shared but, from memory, I can tell that at least 3 attributes are present in all categories.

(4) Is there a chance that the cardinality of the relationship between User and Item is many-to-many instead of one-to-many? For example, in the case of following business rules: A User owns zero-one-or-many Items and An Item is owned by one-to-many Users

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4

I'm having some trouble in recreating a database with a 1:N relationship between Users and Items.

This is pretty straightforward, yes, however each Item belongs to a Category and each Category has a variable number of attributes. E.g:

Cars:   PK | Attribute #1 | Attribute #2
Boats:  PK | Attribute #1 | Attribute #2 | Attribute #3
Planes: PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4

Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.

Although it looked great at first, I couldn't find a way to create the relationship between the Items and the Categories through the database because, at least in my modest database administrator, when creating Foreign Keys, I inform explicitly a database, the table name and column.

In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a user may have with one query.

I could hardcode dynamic queries with the server-side language, but I feel this wrong and not very optimal.

I'm having some trouble in recreating a database with a 1:N relationship between Users and Items.

This is pretty straightforward, yes, however each Item belongs to a Category and each Category has a variable number of attributes. E.g:

Cars:   PK | Attribute #1 | Attribute #2
Boats:  PK | Attribute #1 | Attribute #2 | Attribute #3
Planes: PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4

Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.

Although it looked great at first, I couldn't find a way to create the relationship between the Items and the Categories through the database because, at least in my modest database administrator, when creating Foreign Keys, I inform explicitly a database, the table name and column.

In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a user may have with one query.

I could hardcode dynamic queries with the server-side language, but I feel this wrong and not very optimal.

Additional informations from comments:

(1) How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?

In fact, it's very simple. There are only 5 Categories in total

(2) Will the same Item always belong to the same User (that is, once a given Item has been "assigned" to a certain User it cannot be changed)?

No, they could change. In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.

(3) Are there attributes that are shared by some or all of the Categories?

Not shared but, from memory, I can tell that at least 3 attributes are present in all categories.

(4) Is there a chance that the cardinality of the relationship between User and Item is many-to-many instead of one-to-many? For example, in the case of following business rules: A User owns zero-one-or-many Items and An Item is owned by one-to-many Users

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4

1
source | link