DB Design question

Arc

I am making an App that requires i save info for an Invoice that concers a building and many suites inside that building. There are several buildings and each building has a different amount of suites.

So the invoice saves this info.

Dumpster Costs, Insurance, Fire, Snow REmovale, Lawn Care, COmments, PropTaxes, Gas, Utilities etc...

The owner of the building gets these bills and then splits it up between the tenants..the tenants are charged a certain percentage of the bill depending on how much of the building they are renting.

I am having trouble figuring out the best way to store this information.

Currently i have the DB layed out like this.
BuildingID
SuiteID
InvoiceNumber
InvoiceDate
Comments
Lawn
Dumpster
Snow
Fire
Gas
Utilities
Insurance
PropTaxes

So if a building has 7 suites i would Insert 7 Lines, 1 for each suite. Like so

BuildingID ,SuiteID,InvoiceNum,InvoiceDate,COmments,Lawn..etc
BuildingID ,SuiteID,InvoiceNum,InvoiceDate,COmments,Lawn..etc
BuildingID ,SuiteID,InvoiceNum,InvoiceDate,COmments,Lawn..etc
BuildingID ,SuiteID,InvoiceNum,InvoiceDate,COmments,Lawn..etc
BuildingID ,SuiteID,InvoiceNum,InvoiceDate,COmments,Lawn..etc
BuildingID ,SuiteID,InvoiceNum,InvoiceDate,COmments,Lawn..etc
BuildingID ,SuiteID,InvoiceNum,InvoiceDate,COmments,Lawn..etc

So I end up duplicating some info. THe buildingID is the same for all suites, THe comments are the same, THe InvoiceNum is the same, The InvoiceDate is the same...But the SuiteNum, and amounts in the LawnCare,Dumpster, Snow etc... are different.

So i can't really think of a better way to lay this DB out.... but i know repeating Info is wrong so does anyone know of a better way to do this?

Also this makes it impossible to add an autoincriment Field, because 7 lines may refer to 1 invoice entry and i would want them to all have the same Index/key value, but if i used an autoincrement field they would have different values.

Thanks!
😃 😃

jeremuck

Sure,
have a table of building specific info.
have a different table for suite specific info.
there may be other tables, but since you haven't given much information, I'll stop here.

Make sure those tables have a unique ID such as an INT that is auto_incremented.

Then you have another table that keeps track of it all using only the references.

That way, only one building record actually exists per building.

Hopefully, I gave you enough to start a spark. The hint is you only want to reference the data, not duplicate it.

If you are lucky, you will give more information and Vincente will do the whole thing for you. If you're less lucky, and still confused, maybe he'll make things clearer.

Arc

well i dont think putting the buildnum in 1 table and the suiteinfo in another table helps anything.

Let me see if i can lay out exactly how this is working a little better.

Enter =They enter Info

I have Form That is like this..

Add Invoice For Building Number 921

Invoice Date: EnterYear, EnterMonth

Invoice Number: Enter num

Expenses: Lawn Dumpster Snow Fire Gas Utilities Insurance Taxes

Business Cost: Enter Enter Enter Enter Enter Enter Enter Enter
Suite101: Enter Enter Enter Enter Enter Enter Enter Enter
Suite102: Enter Enter Enter Enter Enter Enter Enter Enter
Suite103: Enter Enter Enter Enter Enter Enter Enter Enter
Suite104: Enter Enter Enter Enter Enter Enter Enter Enter
Suite105: Enter Enter Enter Enter Enter Enter Enter Enter
Suite106: Enter Enter Enter Enter Enter Enter Enter Enter

Comments: Enter Comments

OK, so the user enters the Total cost of the fees in the Business Cost Row then they break it down per tenant on each Suite Row. ALl of this info pertains to 1 building..in this case Building 921

Now i have to add this info to the DB... Keeping in mind there are Several Buildings each with difffering numbers of suites.

The way i am currently doing it is adding a line for each suite in each building. An example of the values i add:

Add row to DB BuildingID=921 ,SuiteID=101,InvoiceNum=234,InvoiceDate2002-03-04,COmments="dsad",Lawn=20.34..etc

Add row to DB
BuildingID=921 ,SuiteID=102,InvoiceNum=234,InvoiceDate2002-03-04,COmments="dsad",Lawn=75.34..etc

And on and on

Then if i wanted to pull this data out By a certain month i would do a Query like

SELECT * FROM tblBuildingInvoices WHERE BuildingId = '921' AND InvoiceDate = '2002-01-09'

Like i said this duplicates some data such as the Comments

ultraslacker

The invoice data really doesn't sound like it belongs in building, but rather in a separate table. Invoice and suite could use a composite primary key.

building
building_id
comment

invoice
invoice_id
building_id
invoicenum
invoicedate

suite
suite_id
building_id
lawn
snow
...

noelc

I am assuming your 'comments' field relates to the invoice and not the building.

I would lay it out like this (* denotes primary key):

Building

BuildingID *
{other building related data here}

Suite

SuiteID
BuildingID [references Building.BuildingID]
{other suite related data here}

Service

ServiceID *
ServiceName
ServiceCost
{other service related data here}

Invoice

InvoiceID *
BuildingID [references Building.BuildingID]
InvoiceNumber
InvoiceDate
Comments
{other invoice related data here}

InvoiceDetail

InvoiceDetailID*
InvoiceID [references Invoice.InvoiceID]
ServiceID [references Service.ServiceID]
ItemCost
{other invoice detail data here}

This avoids the need for maintaining individual columns in the invoice for each service and would allow you to expand the range of services billed for by simply adding a new row to the Services table.

The total for any invoice can be calculated from its detail rows.

If you regularly need to see the data de-normalised (in a flat file format), you can use a view to perform the necessary joins.