Database design

Discussion in 'Webmastering & Programming' started by exiled350, Sep 6, 2017.

  1. exiled350

    exiled350 Limp Gawd

    Messages:
    183
    Joined:
    Jun 26, 2013
    I'm having a hell of a time googling an answer because I really can't formulate a good search query. What I'm looking to do is have a table with personal information in it, then reference a person in another table to have transactional data related to them. The people will rarely change but they need to have things like payments, orders, etc attached to them. Transactions won't be summated, they will be discrete and timestamped. Any ideas or links to resources would be greatly appreciated.
     
  2. modi123

    modi123 2[H]4U

    Messages:
    3,513
    Joined:
    Sep 6, 2006
    Assuming this is in one of the normal SQL arrangements it should be a fairly straight forward application of keys and relationship.

    If it will be a one customer to one transaction then you can have the customer's key in the transaction table. If it can be many to one then you'll need a third table.

    customer table
    ID (int, guid, etc) KEY
    name varchar
    dob datetime
    date_entered datetime

    transaction
    ID (int, guid, etc) KEY
    CUSTOMER_ID (int, guid, etc) foreign key
    info varchar
    date_entered datetime
     
  3. exiled350

    exiled350 Limp Gawd

    Messages:
    183
    Joined:
    Jun 26, 2013
    Thanks!

    This looks like a perfect starting point, at least in terminology. Now I just need to setup a test base and take it from there...
     
  4. gigatexal

    gigatexal [H]ardness Supreme

    Messages:
    7,101
    Joined:
    Jun 22, 2004
    Yup that looks good to me. You can’t presumably have a transaction without a customer.

    Disclaimer: im a DBA

    Also:
    Try to start out as normalized as possible and then denoemalize only when needed.
     
  5. Gulvan

    Gulvan [H]Lite

    Messages:
    117
    Joined:
    Aug 7, 2016
    I actually wonder if it would be good database design to not name things for what they actually are eg) Customer Name but instead XpathY and have a key on a hardcopy for what each field means. With all the breaches going on, I would hope that we would be able to have information stashed that way. But maybe thats just me!
     
  6. Brian_B

    Brian_B Gawd

    Messages:
    702
    Joined:
    Mar 23, 2012
    What your looking for specifically is called a JOIN.

    https://www.w3schools.com/sql/sql_join_inner.asp

    You need to have a common field/key between the two tables to link (Customer ID is a very good choice)

    As far as field names go - I don't think that security-wise it matters much - pretty easy to tell FIELD1 value of Fred Jones is a name, and once they have your database, you're already past way too many security layers. Unless you wanted to seed your tables with a bunch of false trail data - like 2 or 3 random fields for names in with the real one.
     
  7. michalrz

    michalrz 2[H]4U

    Messages:
    2,479
    Joined:
    Jun 4, 2012
    That would surely introduce a lot of bugs. Security through obscurity is not the answer. As long as OP knows and understands how to escape illegal characters in his queries,he should be fine.

    The database schema modi123 proposed looks okay.

    If you want to go a little deeper, you could add a products table:

    upload_2017-9-9_9-49-20.png
     
    gigatexal likes this.
  8. grasshoppa

    grasshoppa n00bie

    Messages:
    18
    Joined:
    Jun 18, 2017
    Agreed on the bug issue; obfuscating tables and fields is never a good idea because it dramatically increases development/support overhead.

    As far as the design, I would take it a step further and introduce some kind of "classification" of transaction. Is this a payment? An adjustment? A charge? A class field would answer that question....

    ...and because I'm me, I'd also do a credit-to-charge table, which allows one to assign which credits are applied to which charges. Mind you, while this gives you a lot of extra data to play with, it does increase the complexity of the database/queries and resultant application. Still, being able to trace where every penny is going is a nice feature to have when we're talking about accounting.
     
  9. michalrz

    michalrz 2[H]4U

    Messages:
    2,479
    Joined:
    Jun 4, 2012

    How bout something like this:

    database2.png
     
  10. grasshoppa

    grasshoppa n00bie

    Messages:
    18
    Joined:
    Jun 18, 2017
    I was thinking more like this:

    Customer
    -id
    ...

    Product
    -id
    -base_cost
    ...

    trans
    -id
    -product_id references product(id)
    -customer_id references customer( id )
    -amount ( copied from product at time of transaction creation )
    -class ( 1 = debit, 2 = credit, ... ) indexed
    ...

    c2d
    -id
    -debit_id references trans(id)
    -credit_id references trans(id)
    -allocated_amount
    ...

    A query might look like this:

    SELECT
    *
    FROM trans debit
    LEFT JOIN c2d ON c2c.debit_id=debit.id
    LEFT JOIN trans credit ON credit.class=2 AND credit.id=c2d.credit_id
    JOIN customer ON customer.id=debit.customer_id
    JOIN product ON product.id=debit.product_id
    WHERE debit.class=1


    I'm geeking out a bit on this, aren't I?
     
    gigatexal likes this.
  11. michalrz

    michalrz 2[H]4U

    Messages:
    2,479
    Joined:
    Jun 4, 2012
    We're not pivoting yet, so, no :D you're fine :D
     
  12. grasshoppa

    grasshoppa n00bie

    Messages:
    18
    Joined:
    Jun 18, 2017
    Don't tempt me.
     
    michalrz likes this.
  13. michalrz

    michalrz 2[H]4U

    Messages:
    2,479
    Joined:
    Jun 4, 2012
    I'm genuinely interested in your witchcraft.

    However we f***g forgot the requirements I think :)

    I have the following questions:
    - could you elaborate on the c2d table? I don't wear a TOP GUN hat at work so I needs to know.
    - in table trans, we have "-amount ( copied from product at time of transaction creation )" does amount mean how many products or transaction value? the "copied" part is the base price (or as I called it MSRP), right?

    If you have the time and patience I wish to learn your ways grasshopper
     
  14. grasshoppa

    grasshoppa n00bie

    Messages:
    18
    Joined:
    Jun 18, 2017
    1) c2d is credit 2 debit. A way of associating any given credit ( payment, adjustment, ect ) to a charge, or debit. So you sell some product for 10 bucks. That goes into the trans table as a class=1 ( debit ). Customer pays 5.00 by cash, 5.00 by CC. That results in two more rows added to trans, with class=2. Now we assign those credits to the debit, resulting in two rows added to c2d. Now, if I ever want to know how a specific debit/charge was satisfied, I can see what credits were applied.

    Sure, in a standard POS situation where you won't necessarily have a customer account that you care about, this method is overkill. However, if you do have customer accounts and you allow payment options, this can give you aging and such.

    2) `amount` was `base_cost`, so we'd add a `quantity` or some such field to trans. Were we to further flesh is out, we'd probably add a `quantity_type` fields too ( `per hour`, `per unit`, `per dozen`, ... ).
     
    michalrz likes this.
  15. michalrz

    michalrz 2[H]4U

    Messages:
    2,479
    Joined:
    Jun 4, 2012
    Dude thanks for sharing your knowledge. It's late over here now (beer o'clock) but I will analyze this and I hope you won't mind (or the OP ;) ) I'd love to brainstorm this. I used to be a PHP coder but one day I decided I was too dumb and quit. But I'm curious George.
     
  16. grasshoppa

    grasshoppa n00bie

    Messages:
    18
    Joined:
    Jun 18, 2017
    Ha! I'm hardly an expert, but I do love this stuff. I somehow fell into a role where I need to integrate a client's product into a customer's database, so I have to analyze the dataplan and come up with the appropriate glue. I enjoy it, it's fun seeing how different developers solve many of the same problems.
     
    michalrz likes this.
  17. ChristianVirtual

    ChristianVirtual [H]ard DCOTM March 2016

    Messages:
    1,402
    Joined:
    Feb 23, 2013
    And now we need to include a table for VAT calculation ... and a decision if in the transactional table we want to separate those. And if some products/transactions have different VAT. ...

    And after another 22000 more tables we have reimplemented SAP ... yeah ...

    But hey; I understand the fun to make a database;
     
    gigatexal likes this.
  18. michalrz

    michalrz 2[H]4U

    Messages:
    2,479
    Joined:
    Jun 4, 2012
    Nice to hear you're on board.

    We'd like to see something Saturday morning :D

    OP has already escaped...
     
  19. ChristianVirtual

    ChristianVirtual [H]ard DCOTM March 2016

    Messages:
    1,402
    Joined:
    Feb 23, 2013
    thats too risky; I'm German and tend to over-engineer ... don't think you want that here. Because then I would add timestamps (valid_from, valid_to) in to the tax table ... because tax can increase ... and ... and ... and ... never finish ...

    oh, and SAP-like valid_to would be 9999/12/31 ... for the valid record with respect to time.:geek:
     
  20. modi123

    modi123 2[H]4U

    Messages:
    3,513
    Joined:
    Sep 6, 2006
    Please.. let's not dirty this post with bringing SAP into things. :yuck:
     
  21. michalrz

    michalrz 2[H]4U

    Messages:
    2,479
    Joined:
    Jun 4, 2012
    You had me at German!



    j/k mate :D

    Edit: phrase of the day is "platform effect"
     
  22. grasshoppa

    grasshoppa n00bie

    Messages:
    18
    Joined:
    Jun 18, 2017
    Not half as much fun as it is making the queries.

    "I can do this in 2 queries!" if often heard around my desk. I feel like a DBA version of Bryony ( that joke will make sense if you've seen the movie ).
     
    gigatexal likes this.