mysql - Need help designing my invoice db structure -


I have a website where customers can buy membership.
Customers can visit payment history at any time and can see that Ben has bought.

I am trying to design DB to make an invoice, but something is not right for me.

My current setup looks like this: 

  + ----------- + ------------ - + --------- + | Invoice | Invoice_item | Products | + ----------- + -------------- + --------- + | ID | ID | ID | | Fk_userID | Characters | Name | | | Volume | Price | | | Zodiac | | | Fk_invoiceID | | + ----------- + -------------- + --------- +

This It seems logical that there is a foreign key in the context of invoice_item in product .
But what if a product has been removed? If they are related, the row in item_list will be deleted or set to tap.

And if you want to see the old invoice it will not work and the product is no longer available.

Then, should the product and item_list be related?

Once defined, you can not delete a product, so a product Add field - I am using an enum in this example, although it can easily be a set of INT or bools (i.e. archival), I use it for, but this is a different answer.

The most important thing is to make sure that the invoice line has been taken from the product in the order of pricing (and details), to ensure that any change in pricing or product name change in the future - Existing invoices do not affect.

I have used the technique (quite successfully) supersidying institutions in a database - so that the original record remains and a new version is inserted whenever the data changes. To do this, I add the following fields:

  • Current ID
  • supersededById
  • previous ID

This creates It asks for a bit more cumbersome - but especially for the address it is important to ensure that the invoices remain intact and changes of that address do not appear in the invoice - eg Changing company name should not change the invoices raised earlier.

Enter image details here 

  create table Do not tap the 'invoice' (`id`, not the primary key (` id`)); Do not tap the tab 'Invoice item' (`id`), 'DRD' VARCHAR (200) tap,` value DCIMAL (11,3) tap, `quantity DCIML (11,3), total` DCIML 11.3) Not zero, `fk_id_Invoice` can not be denied, do not` fk_id_Product`; do not give primary key (`id`); Do not create the tablet `PRODUCT '(` id'), tap AUT agreement, `value` DCIML (11,3) NOUL,` name 'is not VARARA (200) tap,' status' ENUM null, primary (`id `)); Add optional table 'invoice item' Add foreign key (fk_id_Invoice) Reference 'invoice' (`ID`); Optional table 'invoice item' Add foreign key (fk_id_Product) Reference `product` (` id`);

Comments

Post a Comment

Popular posts from this blog

Python SQLAlchemy：AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'schema' -

I tried to create a new database in my project, but when I run the script, I get this error, I have another project, this type of definition worked before, but now it gets a single error. I am using Python 2.7.8 and the version of SQLAlchemy module is 0.9.8. By the way, using a project flask- SQLAlchemy, it works well, I'm confused. Traceback information is as follows: Traceback (most recent call final): File "D: / Projects / IOM / db_create.py", line 4, & lt; Module & gt; From the models the base file "D: \ Projects \ OO-IM \ models.py", in line 15, in the & lt; Module & gt; Column ( 'followed_id', int (), ForeignKey ( 'user.id')) file "C: \ Python27 \ lib \ site-packages \ SQLAlchemy \ SQL \ schema.py", line 369, __new__ schema = metadata. Schema file "C: \ Python27 \ lib \ site-packages \ SQLAlchemy \ SQL \ elements.py", line 662, AttributeError in __getattr__ key): Neither 'column' object nor ...
Read more

java - How not to audit a join table and related entities using Hibernate Envers? -

I use Eners to be hibernate to audit my institutions. I have an audit entity, Foo , which includes a list & lt; Bars & gt; However, I do not want to audit the bar institutions, in the form of properties, I have written that: @inti @edicated public class Foo {@JoinTable (name = "T_FOO_BAR", joinColumns = @JoinColumn (name = "FOO_ID"), inverseJoinColumns = @JoinColumn (Name = "BAR_ID")) @ManyToMany (Cascade = Persist) @ Indited (targetAuditMode = RelationTargetAuditMode.NOT_AUDITED ) Public listing & lt; Bars & gt; GetBars () {Return bars; }} Now, I want to retrieve an amendment of foo : AuditReader reader = AuditReaderFactory.get (GetEntityManager ()); FU modification = (FU) reader.CreativeTeA () .entensEtrevision (Foo Class, 42) .getSingleResult (); Unfortunately, when I want to retrieve all the data (i.e. when it loads lazy times ), error me ORA-00942: table or view is not present , because Tried to query this: Sel...
Read more

mongodb - CakePHP paginator ignoring order, but only for certain values -

While pagging my user model in kppp, I can sort with some values, but not others. For example, I can order results from created or email , for example, but username or reputation seems to come back order by arbitrary command for example with a list of users: $ this-> paginate = array ('condition' => array ( 'User.is_active' = & gt; true), 'border' => 24, 'order' => array ('User.created' => DESC)); Works as expected, but $ this - & gt; Paged = array ('conditions' = & gt; array (' user.isIactive '= & gt; true),' border '= & gt; 24,' command '= & gt; array (' user repeats' = ' & Gt; DESC ')); No, no. I first thought that this could be a database issue, but when I search the database directly, sorted as expected. Note: I am using Mangodebi with the Mongo DB plugin of Ichikawa for the KPPHP; Pagnetizing users are used to work properl...
Read more