Timeline for Inventory database structure when inventory items have varying attributes
Current License: CC BY-SA 3.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 1, 2012 at 23:49 | history | edited | ErikE | CC BY-SA 3.0 |
deleted 1 characters in body
|
| Mar 29, 2012 at 17:13 | history | edited | ErikE | CC BY-SA 3.0 |
fix typo
|
| Mar 24, 2012 at 6:40 | comment | added | TheSecretSquad | @ErikE Thank you for providing such detail and for the code example. Kudos to you for writing/maintaining that. That's exactly the kind of stuff I'm looking to avoid, as it is currently beyond my capability, and like you said, "not something you want to ever try to maintain". I've been working on my ERD the past few days using the supertype/subtype pattern and it is looking pretty much like the hybrid approach you described. I even have a subtype table called NetworkDevices that stores IP Addresses and hostnames. | |
| Mar 24, 2012 at 6:30 | vote | accept | TheSecretSquad | ||
| Mar 19, 2012 at 18:03 | history | edited | ErikE | CC BY-SA 3.0 |
added 498 characters in body
|
| Mar 19, 2012 at 17:16 | history | edited | ErikE | CC BY-SA 3.0 |
added 9648 characters in body
|
| Mar 19, 2012 at 15:01 | comment | added | FrustratedWithFormsDesigner | For querying EAV data, it's not uncommon to build a datamart of relational tables for the data you want to query and then populate them using some script. The queries will run faster, but only against the data that you put in the datamart, and the setup requires a fair bit of planning. | |
| Mar 18, 2012 at 16:43 | comment | added | TheSecretSquad | After examining the the EAV pattern I realized that the values for the attributes are forced to share a data type (all string in this case). Also, querying the EAV setup will be a chore. Supertype/subtype is looking better. My question now, is certain tables allow only specific device types. Do I validate this by putting a device class ID (phone, computer, router) in every table and put a check constraint on that field, or do I exclude that field from the subtype tables and use a trigger on each of them? Please see ERD3 for reference. | |
| Mar 18, 2012 at 1:20 | comment | added | ErikE | Also, time spent automating the use/creation/modification of real subtype tables would, in my opinion, ultimately be best. | |
| Mar 18, 2012 at 1:17 | comment | added | ErikE | I built an EAV database that is in use in a business. Thank God, the set of data is small (though there are dozens of item types) so the performance is not bad. But it would be if the database had more than a few thousand items in it. This experience has led me to really desire to avoid EAV databases in the future if at all possible, because they are so HARD to query. | |
| Mar 17, 2012 at 23:20 | comment | added | TheSecretSquad | I thought of this design where each subtype table had the PK from the parent and the uncommon fields. I thought I could put the type field in the parent and each subtype table and then put a CHECK constraint on them. I decided to avoid this design because it would require a new table anytime a new type of device needs to be tracked, and many one-to-one relationships. It seemed messy and inflexible. I appreciate your input though. | |
| Mar 17, 2012 at 7:02 | history | answered | ErikE | CC BY-SA 3.0 |
