StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
4772849
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
4
CommunityOwnedDate
CreationDate
2011-01-23T07:56:27.080
FavoriteCount
0
LastActivityDate
2011-01-29T16:16:33.337
LastEditDate
2011-01-29T16:16:33.337
LastEditorUserId
484814
OwnerUserId
484814
ParentId
4771528
PostTypeId
2
Score
8
ViewCount
0
LastEditorDisplayName
text
Body
Acknowledgement I have to say, you have done a fantastic job at (a) grasping the Modelling elements provided in your previous question and (b) applying them. You have come a long way in just one day. It is a wonderful reinforcement of the fact that, given the correct education, capable people are empowered to do great things, to go off own their own power. Method Given your stated objective, and your demonstrated capability (not to mention, the first seeker I have dealt with on SO, for Db Design questions who has posted an ERD instead of a bunch DDL), I won't supply answers. I will give you directions and guidance, and you will have to progress your own model. Of course, I will cover specifics as well, but I will cover one or two Subject Areas completely, not all. You can pick that up and apply it to all subject Areas. I have not responded to the core Subject Area, because we are still dealing with Identifying Entities. When that is resolved the <code>Reviews</code>, etc will be easier; the Transaction Entities are Dependent on the Identifying Entities. Direction D.1) I know that I stated that I need to see the whole model. There is one exception. Historic or Temporal or Audit data (eg. the Edit and stored versions). At this early stage, they can be set aside; to be implemented just before completion of the Logical Model. This is in recognition that (a )they are simple Dependents of some parent (b) the parents need to be modelled in relation to all other tables first, and (c) to exclude unnecessary complications, and thus allow us to concentrate on the relevant field. <ul> <li>in particular, you can ignore the tense in the Verb Phrases (every location of a version table would otherwise require <code>Has/Had</code>). Stay with present tense for now, because the focus is modelling, not archiving.</li> </ul> Unresolved U.1) Optional Parent That is completely disallowed. Not just by IDEF1X, but by any notion of Integrity. If the FK Reference is defined, then there must be a Parent. To allow optional parents, the FK Reference must be removed (or not implemented). Such a condition would exclude the result from qualfying as a "Relational database", by definition. Eg. <code>Address:Order</code>. <ul> <li>Of course, in developed countries, an <code>Order</code> must have an <code>Address</code> for legal or taxation reasons; that is separate to the Standard requirement issue. .</li> </ul> U.2) Event <code>Party::PartyAddress</code> is correct; <code>Address::PartyAdress</code> is correct. <code>Event::Address</code> needs work. Address is an Identifying Reference table; if used, it would be the parent, <code>Event</code> would be the child. I leave it to you to identify/model multiple <code>Events</code> to a location, and <code>Events</code> at one or multiple locations. <ul> <li>There may be a Venue involved. Or a <code>EventOccurrence</code></li> <li>But if it is a generic <code>Event</code> which happens at multiple locations, that does not need an Entity, the <code>Address</code> is already in <code>Order</code>.</li> </ul> U.3) Assuming <code>Catalog</code> is an entry in the traditional sense (JCPenney 2011), a list of items for sale or hire. <ul> <li><code>OrderSaleItem</code> is correct</li> <li>Critical point. <code>Catalog</code> is Dependent, and can exist only in the context of a <code>Band</code>, as an Assset. Fine. That means no merchandise other than Band merchandise in the database. Correct ?</li> <li>I can see how "Evening performance with the Blues Brothers" is an <code>Event</code> that can be ordered, invoiced, and paid. Also reviewed, commented, etc.</li> <li>I can't see how <code>Song</code> fits into that. Are the bands selling albums, songs, or both ?</li> <li>Is there no other Band merhandise: concert/event souvenirs; poster; engraved shot glasses ?</li> <li>Consistent with the naming conventions that you reference, and the rest of the database, <code>Catalog</code> (the cotent) should be named <code>Item</code> (the row). You have already (naturally ?) used that in <code>OrderSaleItem</code>,( as opposed to <code>OrderSaleCatalog</code>.</li> </ul> U.4) Genre <ul> <li>No problem with <code>an Item is classified by one-to-many Genres</code>. </li> <li>I think additionally <code>a Genre classifies one-to-many Items</code>. The Relation is one-to-many (which will be resolved as an Associative table when we get to the Physical).</li> </ul> U.5) Favorite The Cardinality of <code>Item::Favorite</code> is reversed. When you correct that, the <code>Favorite</code> Subject Area will require further modelling. <ul> <li>Circular relation or dual paths between the same pair of Entities is a signal of an unresolved model. Generally one is correct and the other is redundant. (There are exceptions, but not here; and when this happens the Verb Phrases differentiate them.)</li> <li>Either <code>Band::Favorite</code> xor <code>Item::Favorite</code> is correct, not both.</li> <li><code>Item::Favorite</code> seems to be correct, because <code>Band</code> is already identified in <code>Item</code></li> <li>Likewise, one <code>Favorite</code> Entity for bands and merchandise does not sound solid. Every Identifier in the single <code>Favorite</code> Entity is a <code>Party</code>. It would break when we Normalise, might as well demand that the Identifiers be clarified at this stage. It is either one Entity with some form of differentiation (<code>FavoriteType</code>) which identifies its treatment; or one <code>Favorite</code> for bands and another for merchandise, in which case differentiation is not required, ambiguity is eliminated. </li> </ul> U.6) Business Rules This is probably the only area you are weak in. General response. You have done the tasks separately (all the modelling vs writing BRs). These do not match the model. When you go through the next cycle, take the Business Rules as directives, and modulate them at the same time, as with the Entities, the Relations, and the Verb Phrases. Question Q.1) User/Friend You have the essence of it perfectly. And the Cardinality of the Relations. (Full treatment on this one.) That is correct for Accepted <code>Friend</code>. <ul> <li>therefore the tense should be past (go with the majority rows)</li> <li><code>Requested</code>, and pending <code>Accepted</code>, are the minority. Easily implemented in a <code>IsAccepted</code> Bit or Boolean.</li> <li>Later you may have <code>IsRejected</code> or <code>IsBlocked</code> (that latter should be a separate Entity).</li> <li>Is that what you require ?</li> </ul> Q.2) What is the basis on which a <code>Person is zero-to-many Users</code> ? Minor Issue M.1) Singular only. M.2) <code>Party Has zero-to-many Addresses</code>. I would think they must have one, in order to transact business (but perhaps not for all <code>Users</code>). M.3) <code>Order May Have zero-to-many Payments</code>. "Requires" means that first <code>Payment</code> has to be inserted at the same time as <code>Order</code>. <ul> <li>Likewise, for any mandatory children (one-to-many as opposed to zero-to-many) that first child must be inserted at the same time as the parent. This is done via Transactions in enterprise databases, because Immediate Constraint Checking (not Deferred) is implemented; and the small end of town fight over silly things like Deferred Constraint Checking is "better" and then spend half their life figuring out how not to get caught in the infinite loops they created, which trap them. MyNonSQL does not have any at all, so nothing to worry about for this implementation. </li> </ul> M.4) <code>OrderSaleItem</code> shoulld be <code>OrderItem</code> xor <code>Order</code> should be <code>OrderSale</code>. Depends if you envision <code>OrderPurchase</code> in the future. <a href="http://www.softwaregems.com.au/Documents/Student%20Resolutions/swisscheese%20Band%20ERD.pdf" rel="noreferrer">▶Subject Area Example◀</a> Readers who are unfamiliar with the Standard for Modelling Relational Databases may find <a href="http://www.softwaregems.com.au/Documents/Documentary%20Examples/IDEF1X%20Notation.pdf" rel="noreferrer">▶IDEF1X Notation◀</a> useful. As stated, I am not providing a finished Data Model, only guidance. This is just one progression of one selected Subject Area. It is not "right" or complete in any way. <ul> <li>Your Verb Phrases are excellent. I have provided alternatives for you to consider, they are not "right" or "better". you need to choose an progress them or your own. The goal being the most concise and accurate VP in each case.</li> <li>No suggestion that <code>Person</code> is correct and <code>User</code> is incorrect, that is pending your answer. But I had to use something in the model; since you have modelled them as separate, a counterpoint may be interesting to evaluate.</li> </ul> Ok, so go ahead and progress the model, then post again (just edit the question, leaving the header paras, and replacing the rest). <h2>V1.1 and Response</h2> That is certainly a progression. I have re-numbered the items in pseudo-legal format, including the section headings, so that we can keep the numbering throughout, and keep adding to it. Actually it really eases the SO editing problems as well. U.3) Would it require an entire rework of the Catalog section or just the identifying relationship that exists with the Band? <ul> <li>No. That's the great thing about working at this level, the decisions you make here will be the railroad tracks that the data runs on, as freight, or does not run on (and thus needs alternate transport and heavy lifting to derive, in the form of masses of code or an additional data warehouse). And the decisions here are cheap (modelling time, paper).</li> <li>Right now an Item exists only in the context of a Band. It is Dependent. To allow non-band merchandise, it needs to be Independent. And then the existing super/subtype cluster needs rework.</li> </ul> Attempted a mod to sell both complete albums or song. Either way they would both be in electronic format only available for download. That is why I listed an Album as being comprised of Songs <ul> <li>Ok. But now you can only sell albums, not songs.</li> </ul> rather then 2 separate entities. <ul> <li>Not sure what you mean (you have two separate entities).</li> <li>It appears you have not seen my <a href="http://www.softwaregems.com.au/Documents/Student%20Resolutions/swisscheese%20Band%20ERD.pdf" rel="noreferrer">▶Subject Area Example◀</a>. Note that if you open it now, it contains bits that I have added V1.1; I have not changed what was there yesterday, the V1.0 response.</li> <li>Actually that means you should go through my V1.0 Answer again, while viewing the Example.</li> </ul> U.5) ... but how to is not clear to me. What am I missing here? <ul> <li>An example of one Entity with differentiation is any of the Supertype/Subtype clusters you have. The Favorite is the Supertype, BandFavourite and ItemFavourite are subtypes; allowing each to reference to Band xor Item respectively.</li> <li>You have modelled ItemFavourite. Now the question is, does the fact of a ItemFavourite imply that the Band is Favourite; or is BandFavourite a discrete fact ? In the example, I have modelled the latter, without the Favourite::ItemFavourite/BandFavourite structure.</li> </ul> Q.1) Yes I would like to have Accepted, Rejected, and Blocked. I am not sure what you are referring to as to how this would change the logical model? <ul> <li>No change (I already stated it was pretty complete) to V1.0, but you might need an additional Entity.</li> <li>You need three Bit or Boolean indicators in Friend. That will service these statuses: <ul> <li><code>Requested</code> (but not Accepted)</li> <li><code>Requested & Accepted</code> . </li> </ul></li> <li>But Blocked is not a Friend (or could have been a Friend previously, but not since being Blocked). So either the Entity name has to change to reflect that (no change to the two Relations) xor Blocked has to be a separate Entity. Two separate meanings for the second Relation leads to complexity, therefore I would go with the latter. </li> </ul> With the former, we have additional statuses: <ul> <li><code>Blocked</code> . <ul> <li>Then the Verb Phrases need change (and I will include the RoleName for clarity), and one of them has a alternate meaning. . </li> <li>(It will be much more clear at the Attribute level Model, that's why we model in pictures, not words; so I have included it.)</li> </ul></li> </ul> Q.2) A person does not have to be a User. They can exist only as a BandMember. Is that what you are asking? <ul> <li>No. Why do we need to differentiate Person and User ? What are the separate actions or attributes ? Thus far, I see Person and User as the same Entity; Person is an User with no activity. </li> <li>This is the last item, holding us back from dealing with the core Subject Area.</li> </ul> M.3) I need to read up more on Constraint Checking to make sure I am understanding things. <ul> <li>Don't worry about that now; I was giving you reason to keep it simple (the NonSQLs appear to simplify things but actually they make it more complex). MyNonSQL has none of those capabilities, so you can eliminate consideration of the platform, and just model the Cardinality meaningfully.</li> </ul> M.4) Depends if you envision OrderPurchase in the future. Can you expand as to what you mean here? <ul> <li>In the context of the Model. You provide the structures to make SalesOrders (of Items). Therefore Item, Order and OrderItem.</li> <li>But if you provided the structures to track PurchaseOrders as well (to purchase Items as well as office supplies, rent, whatever), then you need to differentiate Sales Orders and Purchase Orders. Therefore: <ul> <li>Item</li> <li>OrderSale and OrderSaleItem</li> <li>OrderPurchase and OrderPurchaseItem </li> </ul></li> </ul> Version 1.1 U.2) Event Progressed <ul> <li>EventDate looks good. I would define the Relation as <code>Event Was Perfromed On EvenDate</code>.</li> <li>Whereas ItemGenre is perfect, Event::Venue Needs work. This is a mistake you make consistently, so an explanation is called for. <ul> <li>You have modelled <code>Venue</code> correctly, it is Independent and does exist outside the context of <code>Event</code>. But <code>Event May Be [Held] At zero-to-many [Independent] Venues</code> is not possible.</li> <li>Events are held at many Venues, and Venues host many Events. If that was all, since this is the Logical Level, you can draw a many-to-many Relation, and you are done. At the Physical level, that Relation is resolved by implementing an Associative Table, of which the PK is the two parent PKs, and there is no data. (Enemy is a good example.)</li> <li>But if there is data (eg. you need to track the date or number of attendees or whatever), then it is not an Associative Table, it is another Entity. A Thing that Takes Place between Event and Venue.</li> <li>EventDate is a good candidate. We already have that, and the date. Just add Venue and stir. I would call the Thing that Takes Place between Event and Venue a Performance.</li> </ul></li> <li>Likewise, EventAddress has progressed but is not complete. <ul> <li>Do Events have Addresses or Venues have Addresses ? (model it, no need for words)</li> <li>If Venue: do you need all the historic Addresses for the Venue (like Party), or just the current one (like Order) ?</li> </ul></li> </ul> M.5) SubGenre. Can you explain why SubGenre is (a) Independent and (b) the Relation is Non-Identifying. M.6) <code>Item Is zero-to-many Favourites</code>. Therefore: <code>Item Is a Favourite of zero-to-many Users</code>. Likewise, <code>Each User Chooses zero-to-many Favourites</code>. Therefore <code>Each User Chooses zero-to-many Favourite Items</code>. <h2>V1.2 and Response</h2> Great Progress. U.2) Event Further Progressed Going by your Edit as well as the new Requirements, some yes and some no. All the other Subject Areas of the Data Model are pretty much complete (for Logical), this one area is confused, not nearly as resolved. Partly because of the added Requirements (no complaint, that happens in real life; it is about how you handle it). The main point I will make here is that the Data Model should always model the real world, as opposed to only the business Requirement. That (a) insulates the DM from the effect of change and (b) provides a solid platform for added Requirements. That does not mean you have to model the whole real world, but the parts of it that you do model must reflect reality and not be squished up to fill just the Requirement. Second, there is lack of clarity about the distinctions between Event, Band-Event, Performance, etc. Right now an Event is a Party-Band-Item-Event. That's fine, but it does not work for the new style Event per Requirement. Third, you have a good handle on Address re Party and Order, but not re Venue. <ul> <li>Since you are accepting the Standard-compliant model and therefore the treatment, Address is a Reference table. <ul> <li>It is Independent (square corners)</li> <li>Actually, you can place Address and everything above it on page one; making this part of the model page two, and have Address only on this page.</li> <li>Correctly modelled: A Party has a history of Addresses. They must have at least one current { IsBilling | IsShipping | IsPhysical } Address, based on whatever activity is being executed. </li> <li>Correctly modelled: An Order has one IsBilling Address (if you need IsShipping, you need to add a separate Relation).</li> </ul></li> <li>Address is not a child of Venue (also Independent, correct). I do not think a Venue is located in zero-to-many Addresses. (Maybe that is the old Cardinality-reversed bug, but I am not sure, due to the other confusion re Event and Venue.)</li> <li>Actually Address::Order is suspicious. (Q.3) Do you want Order to reference any valid Address, or a specific address for the Party executing the Order ?</li> <li>Back to Event. Accepting EventDate as declared. That's fine but then Reviews etc, apply to the generic concert and not the single concert which they performed on mushrooms. Go for V1.3. <ul> <li>Your terminology re Event, etc is consistent with the Requirement, etc. but it does not support the Requirement as stated.</li> <li>So let us start using "Event" the way it is used in the real world, and model it that way. What we have been calling "Event", the Party-Band-Item, is actually a Performance. And not a generic one that is scheduled, but a single one at a specific Venue.</li> <li>That is either what you meant with EventDate, or EventDate resolves into Performance.</li> </ul></li> </ul> If you do not mind, I will avoid typing one thousand words, and give you a picture. <a href="http://www.softwaregems.com.au/Documents/Student%20Resolutions/swisscheese%20Band%20ERD%20V1_2.pdf" rel="noreferrer">▶Subject Area Example V1.2◀</a> <ul> <li>Notice that the multiple Bands per Event is resolved.</li> <li>And the Verb Phrases are straight from heaven. An Address hosted multiple Venues, each of which catered multiple Events, each of which is multiple Performances, each of which is one Party-Band-Item.</li> </ul> U.3) Is it time to move the link between Item and Band up to Item and Party instead? With the current design I don't see a possibility to sell merchandise not tied to the band as you have brought up. <ul> <li>First, we need to use Relational terminology, not because I am a pedant, but because the real gurus say it really helps to make the transition to the Relational world.</li> <li>Second, we cannot accomplish that by "moving the Relation".</li> <li>You have to model non-Band merchandise: how you are going to sell it; track it; get paid for it. Whether you want Reviews and Responses, etc. I do not see what Party has to do with it, and right now we are selling Band-Items, not Party-Items. Consider the Referential Integrity issues.</li> </ul> Version 1.2 AR.1) After going through the exercise for FavoriteItem, I feel that Item to Review requires a many-to-many relationship so that is indicated. Necessary? <ul> <li>In V1.1, An Item had many Reviews, and a Review was about one Item. A Person generated many Reviews (one per Item). That is logical.</li> <li><code>A Review is about many Items</code> is not reasonable.</li> <li>If anything, now that FavouriteItem/FavouriteBand is resolved, Review needs likewise resolution and distinction: do we need to differentiate BandReview from ItemReview; does a good/bad ItemReview indicate a good/bad BandReview or are they discrete ? <ul> <li>a Review (as it stands) cannot be about either a Band or an Item. That means two Foreign Keys, and one of the will Null, and Null FKs are not allowed. Item and Band are alreay differentiated, and that differentiation is mature.</li> <li>ItemReviews can be summarised, etc, but that is a different story.</li> </ul></li> </ul> U.7) That leaves us with a new issue to resolve. If a Review can be about a Band or Album or Song or Performance, how do we ensure that Referential Integrity. We do not need an AlbumReview to reference a SongReview, etc. Model it. R.5) The model currently provides Genre at the Item level, that means Album and Song (Merchandise can be disallowed via a CHECK Constraint). Not Band. That may be enough, given that (a) bands change over time, (b) that kind of classification at the Item level is more precise, and (c) Band Genre can be easily derived from their Albums or Songs. <ul> <li>If you need separate Band Genres, you need to add that.</li> <li>What about Event Genre ? If you need it, I think it will be one Genre per Event.</li> <li>Keep in mind that tables like Venue and Genre are serious search criteria in a major database. Vectors for analysis. <ul> <li>The Data Warehouse boys need to add this in as Dimensions to their Facts; in a properly modelled Database, they already exist as Dimensions to Facts. Show me all the Venues with "Folk Music" Events scheduled that attracted more than 10,000 People is dead easy. . </li> </ul></li> <li>Discussion Point. Not saying the above is incorrect. What I have found in both Databases and iTunes is, precision counts. Why have laissez faire Genre::Several things when you can have Genre ::Specific Thing. If you had Genre::Song only, and Song has one Genre only, then Album and Band are precise roll-ups. The way we have it now, it depends on the music knowledge of the data entry person, and Genre::Thing is many, so it is loose. Genre::Song is tight.</li> </ul> R.6) members can show that they will be attending the Event is not modelled. Also clarify interest vs booking vs attendance. R.8) Is not modelled. M.3) The issue is closed, but the Verb Phrase remains unchanged. M.7) Logical Model vis-a-vis Associative tables. Now that that issue is closed, remove any Associative tables for the Logical model; any remaining tables (between two parents) will contain data. That means, go through all the Dependent tables and remove any that do not have data. Thus V1.3 should be less cluttered. M.8) Item is OrderItem. M.9) Now that Party-Person-User is resolved. An Exclusive Subtype structure requires a Discriminator, and the Constrainst will be used to enforce Integrity. Where there are many, PartyType is the way to go. But for just two, a column <code>IsBand</code> or <code>IsPerson</code> is adequate. M.10) You have corrected the cardinality-reversed bug, but some Verb Phrases are still going the wrong way. <h2>27 Jan 11</h2> Actually, I think a lot of these issues would be clearer if we move into the Logical Key/Attribute level (rather than just Entity Relation level). And it is high time we did. For example: Q.3) Order:Address is suspicious. The constraint is not quite correct because that would allow the order to have any Address, not an Address that is specific to the Party executing the order. But since you are MyNonSQL, which has no Referential Integrity, you may not be aware of how it is done in real SQL, so I will provide the FK Definitions, which happen to be RI Constraints as well. It is kind of unfair to expect you to understand my terse statements, which are based in the RM, Normalisation and supported by SQL, when you do not have SQL. <ul> <li>In order for the two constraints to be true, since Party must be the same in each Constraint (there is only one <code>Order.PartyId</code>), only the subset of PartyAddress which belongs to PartyId, will be allowed. </li> </ul> <a href="http://www.softwaregems.com.au/Documents/Student%20Resolutions/swisscheese%20Band%20Address.pdf" rel="noreferrer">▶Address Qualification Example◀</a> Continued in Part II ...
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POData Modeling: Logical Modeling Exercise
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. USPerformanceDBA
UserOwnerUserId
1. USPerformanceDBA
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. POData Modeling: Logical Modeling Exercise
 singulars
 PostTypePostTypeId
 PTQuestion
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
3. VO
 singulars
 PostPostId
 PO
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTAcceptedByOriginator
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.