Results 1 to 10 of 10

Thread: Assistance Visualizing Schema

  1. #1

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Assistance Visualizing Schema

    I am having a difficult time visualizing and working up schema for a project that I am working on and I'm hoping that y'all can provide some input.

    The idea is that I will need to build a website that will allow users to manage their selling activity based. For example, the user will call/email/mail a couple/individual one or more times. The user then sells that couple/individual something. Later on down the road the couple/individual cancels the subscription. The cycle repeats.

    I will be using a traditional database (like SQL Server) to build a relational database design.

    I feel like I should have a few entities here:
    1. State
      • StateId
      • StateName
      • StateSlug
      • StateAbbreviation
    2. Household
      • HouseholdId
      • Address
      • City
      • StateId
      • Zip
    3. Individual
      • IndividualId
      • HouseholdId
      • FirstName
      • LastName
      • Email
      • Phone
    4. ActivityType
      • ActivityTypeId
      • ActivityName
      • ActivitySlug
    5. Activity
      • ActivityId
      • ActivityTypeId
      • ActivityDate


    I feel like this schema would work if users were only doing an activity per individual, but where the curve ball is thrown is that in most cases the user will be doing an activity for a couple.

    So for example, in a household I have John (dad), Jane (mom), and Katin (daughter). The user will want to send one piece of mail to John and Jane and then another piece of mail to Katin.

    I suppose that if the user wanted to do that, they would need to create 2 households with the same address and then tie the mom/dad to one and the daughter to the other. But if I were to do that then I would lose the ability to add a unique constraint on the household's fields.

    What are y'all's thoughts on this?
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  2. #2
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Assistance Visualizing Schema

    A little tired right now but, as solution that I can think of is create another tabled called p.e. "Groups" so, when user send the first email to John and Jane it will get be summed the second to Katin is a sum but by Katin only it will be like so:

    Users:
    Userid
    1
    2

    Groups"
    UserID GroupMerge GroupMembers
    1 1 1 --John
    1 1 2 -- Jane
    1 2 3 -- Katin

    Indvidual:

    ID Name
    1 John
    2 Jane
    3 Katin

    At least at a first glance, that comes into my mind.
    Also what makes me happy is that you need a triple PK in there rather than an ID bigint
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  3. #3
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Assistance Visualizing Schema

    In the design-phase of a Schema, you should leave out all the Foreign-Key-Fields from Entities.
    (because these Fields will irritate more than they help in that phase).

    Let's say your Users want to sell "a subscription for a journal" (where the contract runs for a year).

    Entities (without any ForeignKeys)
    - Contract (ID, DateFrom, DateUntil)
    - Journal (ID, Title)
    - Individual (ID, Firstname, LastName, Email, Phone)
    - Household (ID, Address, City, Zip)

    That's (basically) all the entities you need, to sell your Journal-Subscriptions to somebody
    (via postal-service, phone or Email, no matter if this is a single Individual, or a Couple).

    Leaving out the ForeignKey-Fields now requires you, to make the "connections"
    between "pure Entities" via Intermediate Tables, named like that:
    - Contract_Journal (Contract_ID, Journal_ID)
    - Contract_Individual (Contract_ID, Individual_ID)
    - Household_Individual (Household_ID, Individual_ID)
    - ... etc.

    With just the 3 connection-tables above, you could now already mange the following scenario:
    (assuming an individual caller, a "dad John", who already made contracts with you).

    1. Your system sees the Phone-Nr and looks up the ID from the Individual-Table (from the Phone-Nr)
    2. Your system comes up with 2 Contract_IDs (by querying Contract_Individual via the Individual_ID)
    3. Your system now queries Contract_Individual again, via the 2 retrieved Contract_IDs
    3.1 and finds a contract for "Sport and Fitness" (via Contract_Journal) which was made only by "dad John"
    3.2 and another contract for "House and Garden", which has two records in Contract_Individual (the other Individual_ID pointing to "mom Jane")

    In case it was "daughter Katin" who was calling on behalf of her parents, you could have proceeded this way:
    1. Your system sees the Phone-Nr and looks up the ID from the Individual-Table (from the Phone-Nr)
    2. You retrieve the Houshold_ID by looking up Individual_Household with the current ID
    3. To retrieve all Contract_IDs the Household has ever made, your query would look like:
    Select Contract_ID From Contract_Individual
    Where Individual_ID In (Select Individual_ID From Household_Individual Where Houshold_ID=Current_Household_ID_Of_Katin)

    If you want to send the right Email (e.g. for prolonging a given contract another year),
    you just look-up the records in Contract_Individual (via the current Contract-ID which is one, that's soon "out of date"):
    - getting a single record for Johns "Sport and Fitness"
    - and two records (pointing to "John and Jane") for "House and Garden"
    Easy to send appropriately addressed (and CCed) Emails (with the right salutations) from that info.

    HTH

    Olaf

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: Assistance Visualizing Schema

    @dday - if I understand your needs as described, then I like your layout up to that point.

    With that said, the Activity is the "order", right?

    That Activity simply needs a child table where Individuals can be associated with that ActivityId. I would make a composite PK of ActivityId+IndividualId.

    Now you can have a dozen individuals as part of the activity - even from different households if your UI allows that!

    And all you needs are flags in that record to indicate how each individual will play. "Get Primary EMail?", "Get Only Snail Mail?", "JustForReference?".

    You could also have a simple numeric fields for "GROUP" - all the GROUP 1 people can play one way, GROUP 2 another.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Assistance Visualizing Schema

    I think the issue might be thqt your Household table is really an Address table... I'd rename it to Address, and then create a Household table where you can create a Household ("John and Jane Sith") and then define members of that household ("John" and "Jane") and then associate an address to that Household. Meanwhile Katin could also reference that same Address, or a different address. Tothen know who got what, you just need tge ActivityID, IndividualID/HouseholdID.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Assistance Visualizing Schema

    If you want to "get money for something you sell" - there will automatically be "a contract".

    And a contract typically involves two parties:
    - the Promisor (in dday9's case the Promisor probably being a "juristic person" (aka "a company")
    - and the Promisee

    Also the latter party listed above above (the Promisee) can either be:
    - a single Person (aka Individual)
    - a juristic Person (aka Company)
    - or a "Joint Promisee" (as e.g. "a married couple")

    So, my revised "base-Entities" would be - more formally ("law-abidingly") defined:
    (at this occasion also getting rid of the Entity-Name "Household"):

    Again, all base-entities without any ForeignKey-Fields:
    - Promisor (ID, TitleInContract) ... one or more parties(companies) who sell "the products"
    - Promisee (ID, TitleInContract) ... the party in a contract, which receives the "sold products or services"
    - Contract (ID, DateFrom, DateUntil, etc) ... the contract itself
    - ContractTemplate (ID, Version, Description, JurText) ... different types of "fine-print" (-templates), a given contract is based on
    - Product (ID, Title, Description, Specs, etc) ... in my former list, this was "the sold Journals"
    - Individual (ID, Firstname, LastName, Email, Phone) ... a single Person
    - Company (ID, Name, Email, Phone) ... a single company
    - Address (ID, Street, City, Zip) ... a single Address

    If we now apply a concrete scenario ... as e.g.:
    - a tel-marketer selling a Product (a Journal, named "House and Garden")
    - to new customers ... to a married couple "John and Jane Doe" acting as a "Joint Promisee"

    The (user of the) App has to ensure, that John and Jane exist as Individuals:
    - in 2 records in base-entity-table Individual and 1 record in base-table Address
    - connected via intermediate-table Individual_Address (present as 2 "ID-records")

    The App also has to ensure, that a new Promisee-Record is created
    (since the couple expressed their wish, to act as a "Joint Promisee" in the planned contract):
    - by creating a new record in base-entity-table Promisee (with TitleInContract="John and Jane Doe")
    - and identifying the Individuals which make up that new Joint-Promisee by 2 new "ID-based records" in Promisee_Individual

    The App can now finally ensure a Contract with "Side-connections".
    (assuming, that a Promisor-Record which identifies the "selling party", already exists)
    - by creating 1 new record in base-table Contract (setting specifics like "DateFrom and DateUntil" of the Journal-Subscription)
    - and then finally connecting the 4 necessary IDs of the required base-entities (Contract, Promisor, Promisee, ContractTemplate) -
    ... in a single record of another connection-table, named Contract_Promisor_Promisee_ContractTemplate
    - the "sold Product" (or "sold Products") can be connected via one or multiple records in Contract_Product

    The rule on the base-entity ContractTemplate being:
    - never change, or delete any existing record in that table

    If one has to make "amendments" on an existing JurText within a given Template-Record (on demand of an "insisting Promisee"), then:
    - make a copy of the Template-Record the customer wants to make his own changes on
    - apply a new version in the Version-Field
    - adjust the description
    - finally adjust the JurText (according to the new wishes of the Customer)
    If the company-policy doesn't allow for such "customer-intervention" -
    then the maintenance-frequency on that table is of course reduced, but certainly not zero.


    Well, that's my $0.02 regarding a "clean, very formal and law-abiding" Schema for dday9's scenario.

    Olaf
    Last edited by Schmidt; Feb 10th, 2021 at 11:04 AM.

  7. #7

    Thread Starter
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Assistance Visualizing Schema

    I want to take a minute to thank all of y'all for your input. I greatly appreciate it.

    @Olaf - I decided against setting up an intermediate table for Household/Individual but only because I am fine with the concept that an individual can only every have one household. Otherwise, if I wanted support for an individual to be able to belong to multiple households, then I would likely setup the intermediate table.

    @tg - Your insight that I was using household incorrectly and that what I was really referring to was an address was eye opening. That helped make the other contributor's points more obvious to me.

    Ultimately, this is the schema that I'm going to go with (leaving out the activity tables for brevity):
    Code:
    CREATE TABLE State (
      StateId INT NOT NULL AUTO_INCREMENT,
      StateName VARCHAR(64) NOT NULL,
      StateSlug VARCHAR(64) NOT NULL,
      StateAbbreviation CHAR(2) NOT NULL,
      CONSTRAINT PK_State_StateId PRIMARY KEY (StateId),
      CONSTRAINT UC_State_StateName UNIQUE (StateName),
      CONSTRAINT UC_State_StateSlug UNIQUE (StateSlug),
      CONSTRAINT UC_State_StateAbbreviation UNIQUE (StateAbbreviation)
    );
    
    CREATE TABLE Address (
      AddressId INT NOT NULL AUTO_INCREMENT,
      Street VARCHAR(64) NOT NULL,
      Street2 VARCHAR(64) NULL,
      City VARCHAR(64) NOT NULL,
      StateId INT NOT NULL,
      Zip VARCHAR(9) NOT NULL,
      CONSTRAINT PK_Address_AddressId PRIMARY KEY (AddressId),
      CONSTRAINT FK_Address_StateId FOREIGN KEY (StateId) REFERENCES State(StateId),
      CONSTRAINT UC_Address_Cluster UNIQUE  (Street, Street2, City, StateId, Zip)
    );
    
    CREATE TABLE Household (
      HouseholdId INT NOT NULL AUTO_INCREMENT,
      AddressId INT NOT NULL,
      CONSTRAINT PK_Household_HouseholdId PRIMARY KEY (HouseholdId),
      CONSTRAINT FK_Household_AddressId FOREIGN KEY (AddressId) REFERENCES Address(AddressId)
    );
    
    CREATE TABLE Individual (
      IndividualId INT NOT NULL AUTO_INCREMENT,
      HouseholdId INT NOT NULL,
      FirstName VARCHAR(32) NOT NULL,
      LastName VARCHAR(32) NOT NULL,
      Email VARCHAR(32) NULL,
      Phone CHAR(10) NULL,
      CONSTRAINT PK_Individual_IndividualId PRIMARY KEY (IndividualId),
      CONSTRAINT FK_Individual_HouseholdId FOREIGN KEY (HouseholdId) REFERENCES Household(HouseholdId)
    );
    Fiddle: http://sqlfiddle.com/#!9/20f54e
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  8. #8
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Assistance Visualizing Schema

    In a previous life I used to work on a CMS that did fundraising... so households were something we had to deal with... along with deaths, divorces.... and other things... in fact, it was actually implemented as a Group, where Household was a GroupType, as was "Company" "Club" among others... This allowed someone to belong to multiple groups and be tracked through multiple households over time. We also tracked relationships as part of all this... the threads and strings that were kept... oi!....


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: Assistance Visualizing Schema

    Quote Originally Posted by dday9 View Post
    Ultimately, this is the schema that I'm going to go with (leaving out the activity tables for brevity):
    ...
    Fiddle: http://sqlfiddle.com/#!9/20f54e
    Not sure, whether our postings did overlap - but you might want to take another look into my second one (#6).

    As your Schema currently is, your Household table seems to take over the role of:
    - Joint Promisee

    With quite a lot of "inherent restrictions"... e.g. when you consider the cases of:
    - John, Jane and Katin living under the "same roof"
    - and Katin shall not be part of the "Joint Promisee" (not being part of the contract, which shall only affect "John and Jane")
    - or John and Jane get a divorce - John now living "under another roof" (how do you manage "Dunning" in this case - for still running contracts)

    Another scenario could be a "Joint Promisee" of two neighbours (with different Addresses).

    The proposed schema in #6 does account for all these cases
    (even for single (non-joint) Promisees, which are "juristic Persons" aka companies -
    which could be either "large ones" - but also "a family-business" John and Jane operate, from within their "family-home").

    HTH

    Olaf

  10. #10
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Assistance Visualizing Schema

    Quote Originally Posted by Schmidt View Post
    Not sure, whether our postings did overlap - but you might want to take another look into my second one (#6).

    As your Schema currently is, your Household table seems to take over the role of:
    - Joint Promisee

    With quite a lot of "inherent restrictions"... e.g. when you consider the cases of:
    - John, Jane and Katin living under the "same roof"
    - and Katin shall not be part of the "Joint Promisee" (not being part of the contract, which shall only affect "John and Jane")
    - or John and Jane get a divorce - John now living "under another roof" (how do you manage "Dunning" in this case - for still running contracts)

    Another scenario could be a "Joint Promisee" of two neighbours (with different Addresses).

    The proposed schema in #6 does account for all these cases
    (even for single (non-joint) Promisees, which are "juristic Persons" aka companies -
    which could be either "large ones" - but also "a family-business" John and Jane operate, from within their "family-home").

    HTH

    Olaf
    Yeah, that's the kind of things the groups tble handled in the schema I used to work in... and it also tracked it over time, with begin and end dates... It just depends on how important it is. From experience I can tell you that it gets complicated reaaaaly quick.



    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width