0

At the moment I try to create a good Database structure for storing all Data belonging to an Order/Inquiry.

I use MySQL and Python at the moment and for now it isn't an real DB, just learning and testing!

The Order contains Information from the following Tables of my DB:

  • Customer

  • Address

  • Person

  • Product --> Copied to an Order Product

My questions are:

  • With the Order Product i copy all Product Data to an new Table and "attach" it to the OrderID, that was my best guess to make the Order Products independent from changes to the product later on. Something like a new Price for the Product which should not affect old orders to be changed. Is that a good way to achieve this?

  • I think i have the same problem with Addresses, Persons and also the Customer it self. If a customer changes its address i dont want to transfer that change to old orders. What is the best way to store such data independent or is there any other way of doing this?

  • We have Idicators for our IDs to make it easier to recognize them. An ID for an Address Looks like this "AD0000001" or for an Contact Person "CP0000001" i noticed that i have to seperate the number and the Characters to do auto_increment in my DB. Is it a good practice to save the indicating Characters as an Default Value to my Table? And put them together in my Select Queries?

If i have missed some information or anyhing else, let me know! I will add it to my post!

1
  • Customer ... Person I think a Customer is a Person with some specific attributes combination. I.e. it is one entity, not 2 separate entities.
    – Akina
    Dec 23, 2019 at 15:56

1 Answer 1

1

It would be helpful to see your actual SQL scripts; an English description can be ambiguous. It sounds like you have all the fields from your Address, Customer, and Product tables in your Orders table, to capture a snapshot of the data as it was when the order was placed. Is that right? If so, that's a valid approach, but it will take up a lot of space. Space is cheap, but performance is not: keep your tables lean.

First, do you not separate Orders and OrderDetails? Or is that what OrderProduct is meant to do? You should only need customer and address information in the header table. Your detail table would then typically be at a level of specific line items, with quantities and prices. In some cases, a logical order might be shipped to multiple locations, or at separate times, and you'd need Address, DateShipped, DateReceived, DateReturned, etc., at the line item level.

Second, changing values for customers and addresses. It's good that you're giving this some thought. The topic you need to read up on is "slowly-changing dimensions:" today, customer #123, Mr. Smith, lives on 101 Main Street, next year she's Ms. Smith and she lives on 1st Avenue, but she's still customer #123. There are many approaches to handling this problem. Typically, rather than recording this information in the Orders table, you'd record each version of the customer as a record in the Customers table, with a persistent customer number (123) and a version number (1 for Main Street, 2 for 1st Avenue), something like this:

 CustomerSK CustomerID VersionID FirstName LastName Address DateEffective DateExpired IsCurrent
 ---------- ---------- --------- --------- -------- ------- ------------- ------------ ---------
 42942      123        1         Robert    Smith    10001   2018-12-07    2019-12-22  0
 42943      123        2         Roberta   Smith    10002   2019-12-23    NULL        1

 OrderID CustomerSK DatePlaced
 ------- ---------- ----------
 103101  42942      2018-07-19
 103102  42943      2019-12-24

You may have a calculated field which concatenates customer ID and version ID to make 123001 and 123002, or create a new auto-increment field for the table, with no relation to the customer IDs. This is known as a "synthetic key", and is labeled CustomerSK in the sample above.

Your Orders table would either have two fields, CustomerID and VersionID, or a single CustomerSK field. You use this field, or pair of fields, to join to the Customers table and get the name and address as of when the order was placed. You could join to Customers again, on C1.CustomerID = C2.CustomerID AND C2.IsCurrent = 1, to get the customer's name and address right now.

IsCurrent can be a calculated field on DateExpires IS NULL. Alternatively, you may prefer a far future value like 2100-12-31 for customer records which have not yet expired. Be sure to have a unique constraint on { CustomerID, VersionID } and on { CustomerID } WHERE IsCurrent = 1.

In a real example, you'd have multiple addresses for each customer. The same principle holds: when an address changes, record the new value, mark the old value as expired, and mark the new value as current.

Third, concatenating a text prefix to a numeric code is not going to be ideal for IDs. You should distinguish between people-friendly IDs and database IDs. The former may be alphanumeric, to help people recognize them, but data integrity and joins should be based on the latter, for best performance. It's easy enough to display a prefix before the numeric ID, if necessary, in GUIs. Consider dates: you always store them using a DATE data type, never as a number or text, but you can format them however the user likes when displaying data.

I'm not sure what distinction you're drawing between customers and people. Pedantically, it may seem strange to put prospects in a Customers table if they've never actually ordered anything, so you may prefer to call it People or Contacts, but what information would you store for a customer that you wouldn't for anyone else? LastOrderID or DateLastOrder, perhaps? Those may be sparse columns in a table which includes prospective customers, but NULLs don't take up much space.

5
  • First of all, thank you very much! At the moment i dont have any scripts, i just started with "pen and paper"-planing for now! I have a few questions: 1. In the Order is just the AddressID, CustomerID, PersonID and so on. Not all the data 2. Yes Order and OrderProduct seperate the Details and the Order Data like Customer etc. Quantity and Prices is saved into the OrderProduct 2. I totally get your point with the slowly changing dimensions. But i thought that i would declare CustomerID as a PrimaryKey to use auto_increment on it. How do i achieve to have #123 multiple times? [...]
    – K-Doe
    Dec 23, 2019 at 16:18
  • to 2. So CustomerID isnt the value i auto_increment? There is another ID like RowID or someting else? 3. So you would only add the text prefix in the GUI and not in the DB ?
    – K-Doe
    Dec 23, 2019 at 16:20
  • 1
    Glad to help! You need two IDs in the Customers table, and each of the other SCDs: one for the logical customer (#123, in my example), and one for the customer/version (v1 and v2). The pair of them would be unique: { CustomerID, VersionID }. Your Orders table could point to these with a double-barreled join, but for better performance you'd have a separate auto-increment ID in the Customers table, and you'd store this in the Orders table. I'll expand on my example. Dec 23, 2019 at 22:29
  • Got it! I think i have one last question before starting my first test. I know now hot to auto_increment CustomerSK, also i have a way to increment VersionID without any problems. But if i create a new CustomerSK how do i ensure that i do not create CustomerID a second time? Could also be that i am just over-cautious, but i am quite unsure with that!
    – K-Doe
    Dec 24, 2019 at 12:20
  • In the Customers table, it would be perfectly normal to have multiple records with the same CustomerID; you're asking how to generate sequential IDs, since it's not simply an auto-increment field? Most databases have some kind of SEQUENCE object for such purposes. It looks like MySQL doesn't, but you could create a table specifically for creating CustomerIDs. If there's any information about a customer that would never change - perhaps the system which sourced them, such as Salesforce or Marketo - you could store that there. Dec 26, 2019 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.