Database design

exiled350

exiled350

2[H]4U
Joined
Jun 26, 2013
Messages
2,669
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.
 
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
 
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...
 
modi123 said:
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
Click to expand...

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.
 
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!
 
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.
 
Gulvan said:
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!
Click to expand...

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
 
michalrz said:
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:
Click to expand...

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.
 
grasshoppa said:
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.
Click to expand...


How bout something like this:

database2.png
 
michalrz said:
How bout something like this:
Click to expand...

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?
 
grasshoppa said:
Don't tempt me.
Click to expand...

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
 
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`, ... ).
 
grasshoppa said:
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`, ... ).
Click to expand...

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.
 
michalrz said:
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.
Click to expand...
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.
 
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;
 
ChristianVirtual said:
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;
Click to expand...

Nice to hear you're on board.

We'd like to see something Saturday morning :D

OP has already escaped...
 
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:
 
Please.. let's not dirty this post with bringing SAP into things. :yuck:
 
ChristianVirtual said:
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:
Click to expand...

You had me at German!



j/k mate :D

Edit: phrase of the day is "platform effect"
 
ChristianVirtual said:
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;
Click to expand...
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 ).
 
You must log in or register to reply here.
Back
Top