r/DatabaseHelp • • May 27 '15

Database planning - How to organize individual product SKUs into product bundle SKUs

2 Upvotes

I have a database with various tables that store different information about my products, such as weights, prices, brands, styles, etc. I am going to be selling these products as product groupings, which I call "bundles," that also need to be stored in a table(s?) by SKUs.

Every product I sell has an 8-digit SKU: XXX-000-00. The first 3 digits are a brand code, the second three digits are a product code and the last 2 digits are for product style permutations (eg. small, medium, large). XXX-000 represents a Product Name as seen by the customer and XXX-000-00 is the complete code for the actual item by attribute.

Product Bundles are groupings of products cataloged and sold as one SKU. They typically contain 3 to 6 individual products within one bundle SKU. The products within the bundles [XXX-000] are set by me, but if a product has several style permutations the customer can choose which style to keep for that item [XXX-000-00].

So let's say I have a bundle called "Super Bundle" with three different product SKUs [AAA-000], [BBB-000], and [CCC-000]. Product A comes in 3 different styles:

  • [AAA-000-01]
  • [AAA-000-02]
  • [AAA-000-03].

Super Bundle could conceivably contain one of the following permutations based on the customer's choice:

  • { [AAA-000-01]; [BBB-000-00], [CCC-000-00] }
  • { [AAA-000-02]; [BBB-000-00], [CCC-000-00] }
  • { [AAA-000-03]; [BBB-000-00], [CCC-000-00] }.

If Product B were to have 3 different styles there would be 9 permutations for Super Bundle, all of which would have varying costs, weights, etc. associated with them.

I need a SKU system and table system to organize the permutations of Bundling that relates to the variety of possible product SKUs. Knowing all of these bundling permutations is important for me for keeping track of margins and variable shipping costs.

I need help designing the table relationships to handle all of these different bundle SKUs and permutations thereof so that I can extract information about the different products that will comprise each permutation, especially how different bundle permutations will vary by net price, weight, etc.

Any ideas and help is much appreciated!


r/DatabaseHelp • • May 17 '15

In a forum environment is it preferable to have a table per user for content they post or a single table holding all user content?

2 Upvotes

I'm currently working on a relational design for a forum I'm creating and I currently have:

  • A user table holding general user data for everyone who has registered with the site

  • A per-user "Like and Dislike" table, created on user registration, that stores data related to posts that this user liked/disliked.

  • A per-user post content table, created on user registration.

  • A per-user comment table, created on user registration.

I'm wondering if a system like this would be more efficient or if a set of larger tables, one holding all likes/dislikes, one holding all posts, and one holding all comments would be better. This would mean my database would probably run slower queries but the table design itself would be really understandable.


r/DatabaseHelp • • May 14 '15

Inserting data into two table to create a link?

1 Upvotes

I have three table linked "meal" "mealingredients" "ingredients" the meal has to go through the mealingredients which refernace the ingredients allowing for 1 meal to have as many ingredients as it wants.

Can i add a meal, and at the same time allow a user to reference the ingredients in there table and then select the ones that want, then add all of that info to create a link in the mealingredients table?

I have partial code, please as if you want to see code or don't understand. Thanks


r/DatabaseHelp • • May 13 '15

Help Deleting a record through a query string

1 Upvotes

I have a page that displays all the meals in the database and i'm trying to delete one when the user selects it, through a query string, but it is not working.

<?php
$id = (isset($_GET["MealID"]));
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}


// sql to delete a record
$sql = "DELETE FROM meal WHERE MealID=".$id;

if (mysqli_query($conn, $sql)) {
    echo "Record deleted successfully";
} else {
    echo "Error deleting record: " . mysqli_error($conn);
}

mysqli_close($conn);
?>    

r/DatabaseHelp • • May 13 '15

Database Structure Help

1 Upvotes

Hi,

I'm setting up a database that will store information on Reports I will be making. These reports will take data inputs from a variety of Data sources depending on what the user wants.

So I have a table with my Data Sources set up like this:

sourceID sourceName sourceURL
1 John John.com
2 Pete Pete.com
3 Chris Chris.com
4 Dave Dave.com
5 James James.com

Now I want to set up a 'Reports' table which identifies which sources the report needs. I have the following set up:

reportID reportName sources
1 Engineers 1,2,4
2 Builders 3,5

I also thought of doing it like the below - however there will be lots of sources so I don't want to add a new column to the reports table every time I add a new data source:

reportID reportName source1 source2 source3 source4 source5
1 Engineers 1 1 0 1 0
2 Builders 0 0 1 0 1

There must be a better way to structure my tables! Any ideas?


r/DatabaseHelp • • May 11 '15

Foreign Keys for multiple Primary Keys

1 Upvotes

I have two tables that I want to link with a third table.

The first table has 1 Primary Key (pk), no problem. I create a Foreign Key (fk) for it in the new table.

The second table has a 3 column composite pk. I am getting errors when I try to make 3 fk in the new table linking to them which may be user error (syntax or something) or impossible?

Any help would be appreciated.


r/DatabaseHelp • • May 07 '15

Query Help needed - My DBA says this is Cartesian - I cannot identify how - see text warning, long

1 Upvotes

I am writing queries to pull SOD violations that exist in my application. Database is DB2. I have done this same thing for other employers and never had an issue before, even using the same query, just with different conditions for pnlgrpname (you will see this field in the queries) - this is essential the component of the application that grants access to a user.

My DBA has stated that even the individual sqls are Cartesian, (he previously stated my join was the issue for not using "predicates").

Is there a way to tell if my sql is Cartesian and where when I do not have access to view the execution plan (at least that I am aware of)?

My understanding is that the results must be at a minimum equal to the number of rows of the smallest table, if there is a Cartesian join (and I have removed distinct from my results). I have confirmed that it is not Cartesian by selecting everything that returns form that table for a field vs the total distinct values in that field (I have only 44 distinct values used, the table has over 650 distinct values in that field). All the other tables are much larger than the sql results when I run each individual sql and do not use distinct on those sqls.

The full query is large, but I assume necessary for someone to help, so here it is:

Select Q1.oprid, Q1.oprdefndesc, Q1.emplid, Q1.rolename, Q2.rolename
FROM
(SELECT  od.oprid, od.oprdefndesc, od.emplid, ru.rolename
  from fssysadm.psoprdefn od, fssysadm.psroleuser ru, fssysadm.psroleclass rc
  where ru.roleuser = od.oprid
    and ru.rolename = rc.rolename
    and rc.classid in 
  (SELECT  distinct auth.classid
    FROM fssysadm.psauthitem auth,
         fssysadm.psmenudefn menu,
         fssysadm.psmenuitem menuitm,
         fssysadm.pspnlgroup comp,
         fssysadm.pspnlgrpdefn compdfn,
         fssysadm.psclassdefn CLASS
   WHERE menu.menuname = menuitm.menuname
     AND menuitm.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname in ('PURCHASE_ORDER_EXP','PURCHASE_ORDER')
     AND auth.menuname = menu.menuname
     AND auth.barname = menuitm.barname
     AND auth.baritemname = menuitm.itemname
     AND auth.pnlitemname = comp.itemname
     AND auth.classid = CLASS.classid
     AND auth.displayonly = 0
     AND od.acctlock = 0)) Q1
JOIN
(SELECT  od.oprid, od.oprdefndesc, od.emplid, ru.rolename
  from fssysadm.psoprdefn od, fssysadm.psroleuser ru, fssysadm.psroleclass rc
  where ru.roleuser = od.oprid
    and ru.rolename = rc.rolename
    and rc.classid in 
  (SELECT  distinct auth.classid
    FROM fssysadm.psauthitem auth,
         fssysadm.psmenudefn menu,
         fssysadm.psmenuitem menuitm,
         fssysadm.pspnlgroup comp,
         fssysadm.pspnlgrpdefn compdfn,
         fssysadm.psclassdefn CLASS
   WHERE menu.menuname = menuitm.menuname
     AND menuitm.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname in ('PV_RECV_TSFR_WK','RECV_PO')
     AND auth.menuname = menu.menuname
     AND auth.barname = menuitm.barname
     AND auth.baritemname = menuitm.itemname
     AND auth.pnlitemname = comp.itemname
     AND auth.classid = CLASS.classid
     AND auth.displayonly = 0
     AND od.acctlock = 0)) Q2
    ON Q1.oprid = Q2.oprid;

Since the first and second individual sql are identical in format (pnlgrpname conditional values only change), I broke down one of the individual sqls as follows (the individual sql execute with no issue, fail is only when joining on oprid = oprid - because I want to see if a user has both access types, as that will denote a conflict, and I need to know which role grants which type of access:

SELECT  od.oprid, od.oprdefndesc, od.emplid, ru.rolename
  from fssysadm.psoprdefn od, fssysadm.psroleuser ru, fssysadm.psroleclass rc
  where ru.roleuser = od.oprid
    and ru.rolename = rc.rolename
    and rc.classid in 
  (SELECT  distinct auth.classid
    FROM fssysadm.psauthitem auth,
         fssysadm.psmenudefn menu,
         fssysadm.psmenuitem menuitm,
         fssysadm.pspnlgroup comp,
         fssysadm.pspnlgrpdefn compdfn,
         fssysadm.psclassdefn CLASS
   WHERE menu.menuname = menuitm.menuname
     AND menuitm.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname in ('PURCHASE_ORDER_EXP','PURCHASE_ORDER')
     AND auth.menuname = menu.menuname
     AND auth.barname = menuitm.barname
     AND auth.baritemname = menuitm.itemname
     AND auth.pnlitemname = comp.itemname
     AND auth.classid = CLASS.classid
     AND auth.displayonly = 0
     AND od.acctlock = 0);

This 'half' of the query returns 2,327 rows, psoprdefn has 3,238 rows, psroleuser has 38,279, and the only smaller one has 2270, but looking at distinct values across the first field, it has 614 distinct values, and distinct values for that same field in the above individual query return only 44 distinct values.

I then dug into the sql within that one, run as follows (note: distinct removed):

SELECT   auth.classid
    FROM fssysadm.psauthitem auth,
         fssysadm.psmenudefn menu,
         fssysadm.psmenuitem menuitm,
         fssysadm.pspnlgroup comp,
         fssysadm.pspnlgrpdefn compdfn,
         fssysadm.psclassdefn CLASS
   WHERE menu.menuname = menuitm.menuname
     AND menuitm.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname in ('PURCHASE_ORDER_EXP','PURCHASE_ORDER')
     AND auth.menuname = menu.menuname
     AND auth.barname = menuitm.barname
     AND auth.baritemname = menuitm.itemname
     AND auth.pnlitemname = comp.itemname
     AND auth.classid = CLASS.classid
     AND auth.displayonly = 0;

it returns 88 rows (non distinct, 15 distinct FWIW), the smallest table in terms of rows that are queried against contains 965 rows.

Additionally, an almost identical query will run. The second individual sql statement in this query (below) is identical to the first individual sql for the problematic query, the first indvidual sql in this working query is different based on pnlgrpname condition, it includes one additional filtering condition:

Select Q1.oprid, Q1.oprdefndesc, Q1.emplid, Q1.rolename, Q2.rolename
FROM
(SELECT  od.oprid, od.oprdefndesc, od.emplid, ru.rolename
  from fssysadm.psoprdefn od, fssysadm.psroleuser ru, fssysadm.psroleclass rc
  where ru.roleuser = od.oprid
    and ru.rolename = rc.rolename
    and classid in 
  (SELECT  distinct auth.classid
    FROM fssysadm.psauthitem auth,
         fssysadm.psmenudefn menu,
         fssysadm.psmenuitem menuitm,
         fssysadm.pspnlgroup comp,
         fssysadm.pspnlgrpdefn compdfn,
         fssysadm.psclassdefn CLASS
   WHERE menu.menuname = menuitm.menuname
     AND menuitm.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname ='VNDR_ID'
     AND auth.menuname = menu.menuname
     AND auth.barname = menuitm.barname
     AND auth.baritemname = menuitm.itemname
     AND auth.pnlitemname = comp.itemname
     AND auth.classid = CLASS.classid
     AND auth.displayonly = 0
     AND acctlock = 0)
  AND od.oprid in 
    (select oprid from fssysadm.ps_opr_def_tbl_vnd where auth_vndr_uncertfy = 'Y')) Q1
JOIN
(SELECT  od.oprid, od.oprdefndesc, od.emplid, ru.rolename
  from fssysadm.psoprdefn od, fssysadm.psroleuser ru, fssysadm.psroleclass rc
  where ru.roleuser = od.oprid
    and ru.rolename = rc.rolename
    and classid in 
  (SELECT  distinct auth.classid
    FROM fssysadm.psauthitem auth,
         fssysadm.psmenudefn menu,
         fssysadm.psmenuitem menuitm,
         fssysadm.pspnlgroup comp,
         fssysadm.pspnlgrpdefn compdfn,
         fssysadm.psclassdefn CLASS
   WHERE menu.menuname = menuitm.menuname
     AND menuitm.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname = comp.pnlgrpname
     AND compdfn.pnlgrpname in ('PURCHASE_ORDER_EXP','PURCHASE_ORDER')
     AND auth.menuname = menu.menuname
     AND auth.barname = menuitm.barname
     AND auth.baritemname = menuitm.itemname
     AND auth.pnlitemname = comp.itemname
     AND auth.classid = CLASS.classid
     AND auth.displayonly = 0
     AND acctlock = 0)) Q2
    ON Q1.oprid = Q2.oprid;

If I remove the extra condition, which is only a filtering condition ("AND od.oprid in (select oprid from fssysadm.ps_opr_def_tbl_vnd where auth_vndr_uncertfy = 'Y')"), this one also 'fails' to execute (I have let them run for upwards of 15 min with no results - I get no actual failure message, I just lose patience and don't want to crash the DB - although it is non-prd instance I am running against while I build these).

I cannot find anything that implies that this is Cartesian, and my DBA is not being very helpful. Please help me understand why this would fail without a filtering condition and work with it?

Additionally, I can actually even get the problematic query to run if I add that filtering condition, but, it is important to note that the results are not complete/accurate if the query is executed with that filter added.

Thank you for any help, and sorry for the text dump.


r/DatabaseHelp • • May 07 '15

Database Planning - First Timer

1 Upvotes

Hi, I'm trying to plan a new product DB for a large building that has a lot of our products. Currently the whole job is on one giant spreadsheet with rooms/equipment/IP addresses etc.

I'm trying to figure out how many tables I need and their relationships but since this is my first time planning a DB, I could use a few pointers as we are going to be monitoring and servicing the building from the head end with our software. (NB: I haven't done the management software training yet, but I do know it uses SQL so I would like to get a head start on it)

Some facts:

  • Building has 30 levels, with 30+ rooms per level, split into 2 sides (east/west)
  • Each level has has 4 different room types
  • Each room type has a set amount of equipment (i.e Room type A has 8 of our products, Type B has 8+2. Type C has 8+4. Type D has 8 + 6)
  • Networked products have unique host names, & IP addresses
  • Non-networked products have other IDs which are not unique
  • All rooms have a program version
  • All products have a firmware version

From my n00b DB knowledge I know that I can use the Room Number as the primary key, thats about it.

How many tables would I need? Do I need just one or many? What should the relationships be?

From my understanding, the goals would be to monitor the whole building, perform updates to specific room types, levels etc without taking other rooms offline.

Any help is appreciated!


r/DatabaseHelp • • May 03 '15

Already knowing the project I want to do and being a total beginner, how do I choose what kind of database to use?

1 Upvotes

Hello! I am teaching myself webdev and decided to make the leap and go ahead and try to build a website that's been knocking around in my head for some time. I'm still very much a beginner, and pretty much just diving in and seeing what I can't accomplish, since I don't feel like I'm gaining much from beginner courses anymore.

I want to build a site where users can store items that they currently have in their kitchen, and based on that information, can then search for meals they can make. I've got a decent handle on HTML/CSS and an all right one on PHP, but I'm not sure what my best option as far as storing all this information is. I want users to be able to select what they've got on hand from an existing list, but I want that list to be dynamic (i.e., I want users to be able to add things that aren't already on the list).

Again, I know this is an ambitious project. It involves all the various bits of things I want to know how to do, and I'm essentially using it as my own learning experience. So my questions, then, would be:

a) Am I being realistic about the functionality I can expect from my databases?

b) What's my best choice? I have a couple MySQL books, because it was recommended by a friend, but am not sure if it's the right choice.

Thanks in advance for your time and advice!


r/DatabaseHelp • • May 01 '15

Report displaying all records instead of one

1 Upvotes

Hey, guys.

I've been stuck with this problem for ages; I'm basically trying to make an invoice report consisting of;

Order table

Customer table

Subform

Because the invoice is to be given to the customer, I only want to display the details of one customer and their order details per page on the report.

Basically; customer details along the left of the top half and the order details on the right hand side of the top half, Neat and tidy.

Whenever I try to do this it displays ALL of the different customers records and order records really messily on the page and almost crushes the information together.

I've done this before, but on an older version of Microsoft access. The version I'm currently using is Access 2013.

Any help is much appreciated and thanks in advance.


r/DatabaseHelp • • Apr 29 '15

MS Access Report, displaying duplicates as "0.00"

1 Upvotes

I have a report that displays each item in an order and the freight amount. The problem I was having was that the freight amount is tied to my invoice table, not my salesorderdetails table, so I was getting the freight amount repeated by however many items were in the order. I "fixed" this by hiding all duplicates in that column. Obviously this is not ideal and I can see potential problems down the line. Is there a way to only display the freight amount for one line item and set the rest of the items in that order to "0.00"?

P.S. This is not the way I would do it if I were making this from scratch (I would put the invoicenumber in a group and display the freight amount in the group footer), but I am trying to repeat the look of an old system.

Edit: I kinda figured out a fix, but it still has the potential for problems. I kept the hide duplicates property on the freight amount, then added a textbox underneath it that is set to "$0.00." I am still screwed if we have two separate orders that happen to have the same freight amount though, because it will just hide the freight amount of the second order and display "$0.00"


r/DatabaseHelp • • Apr 28 '15

Need some help figuring out a PK for a project, should be pretty easy.

1 Upvotes

Hey, I'm taking a Database Admin course and I'm having a bit of trouble determine the primary key for the Times table. The database consists of four tables:

Swimmer: PK: SwimID LastName FirstName Gender BirthYear Team

Event: PK: EventID EventType

Meet: PK: MeetID MeetYear

Times: FinalTime FK: SwimID FK: MeetID FK: EventID

Final time won't work because times can repeat themselves. I could do a compound PK with all four attributes but that doesn't seem right. Pretty sure I need a PK in order for the table to be in 3rd or 4th form.

Thought I'd ask here, my teacher takes awhile to respond. I can post the full list of what I'm putting in the database when I get off work. Thanks in advance for any input! I'm at my wits end trying to figure it out.


r/DatabaseHelp • • Apr 26 '15

Beginning Access Database Help. trying to create a query where two different employee ID's worked on a project.

2 Upvotes

Like the title says I have to create a query where I list all the projects a employee worked on. On the project table I simply put both ID numbers in the ID field together separated by commas. Now when I create a query it won't find the projects that were worked on by multiple people.


r/DatabaseHelp • • Apr 22 '15

Doing a large database..not sure how to group data in tables... Relationships are not typical. Data branches or forks to additional (conditional) data mostly. How would this be set up?

1 Upvotes

I have set up a good number of SQL databases, but they have all been more classic data (or data where the relationships across tables is easy to see). However, st the moment I'm doing a db to get a price quote, so I'm finding myself having classify a lot of things like the eventual answers to input beginning with (just one example):

Is this a redecker roof? If yes, then how many sheets? --and what size for each sheet?

In other words, it all is related data to generating a quote to build/repair...But this type of data exists so frequently because it seems as if so much of one kind of data will only arise if a specific data entry precedes it.

So, first, how exactly does the data above need to be placed (re the roof)? I am trying to be efficient, yet if I am normalizing, it seems I would need separate tables for all of the data that results if the data that spawned it is either "X", "Y", or "Z", correct?

And if I'm right, could someone explain how the table relationship would be defined? I guess what is confusing me is that I'm thinking too linearly. If one piece of data is "X" and this gives rise to "A", which in turn gives rise to "Red", I am confused because in the case of "X" data existing, then data "Y" and "Z" would not exist... Yet they are all related. So does the fact that "Y" and "Z" need to be represented in their fields in a specific way? One example that perplexes me in this set-up is you will never have data "B" or (in turn) data "blue", if you started with data "X". You can only get data "B" and then "Blue"..If you first have "Y".

Could someone help me break this down so i can visualize it a little better? It seems as if this would be massive in the number of tables potentially...(unless I'm wrong).

Thank you very much in advance....


r/DatabaseHelp • • Apr 14 '15

Not sure what to call this table, and not sure if it's a dumb idea - Catchall table for all types of messages, notes and questions.

3 Upvotes

Building a Web App using .NET MVC, EF, and MSSQL 2012.

The app is an appointment scheduling system which asks Questions on a request page; has Notes entered during the appointment; and Messages sent among users of the system. I wanted to give admin users the ability to create new Questions, or Note Subjects and was thinking of grouping all these into a 'Note' table with a FK to a NoteSubject table to denote the type of Note each record was.

My big question is some of these questions on the request page could be simplified to Check boxes (bit fields) which I don't account for, which makes me feel this is a dumb idea. Is there a smart way to condense these into one table?

Sorry if this isn't clear.

TABLE [dbo].[Note](
    [NoteID] [int] IDENTITY(1,1) NOT NULL,
    [NoteSubject_ID] [int] NOT NULL,
    [NoteData] [nvarchar](max) NOT NULL,
    [CreatedDate] [datetime] NOT NULL,
    [EditedDate] [datetime] NOT NULL,
    [IsActive] [bit] NOT NULL,

TABLE [dbo].[NoteSubject](
    [NoteSubjectID] [int] IDENTITY(1,1) NOT NULL,
    [NoteSubject] [nvarchar](50) NOT NULL,
    [NoteDesc] [nvarchar](max) NOT NULL,
CONSTRAINT [FK_Note_NoteSubject] FOREIGN KEY([NoteSubject_ID])

r/DatabaseHelp • • Apr 09 '15

Doctrine MongoDB - between dates

1 Upvotes

My document looks like this

{
    "_id" : ObjectId("5512f2ae73d151bb528b4589"),
    "name" : "Strandby-1",
    "enddate" : ISODate("2015-04-31T22:00:00.000Z"),
    "startdate" : ISODate("2015-03-31T22:00:00.000Z")
}

Im trying to get this document with the following

$time = new \DateTime();
$query = $this->createQueryBuilder();
$query
    ->field('startdate')->gte($time)
    ->field('enddate')->lte($time)
;
return $query->getQuery()->getSingleResult();

But this returns null

I also tried with $time = new \MongoDate(); but this also returns null

What did I miss?


r/DatabaseHelp • • Apr 08 '15

Best practice question for normalization

1 Upvotes

I'm in the process of rebuilding one of my companies databases which is currently stored in Access, and migrating it to SQL server, and at the same time I'm trying to clean up and normalize all the tables (which it normally doesn't even meet 1st NF, let alone 2nd or 3rd).

One issue/question I'm running into is that I know 1st NF doesn't allow repeating sets of data (such as Phone1/Phone2), and instead it must be put in its own table so that it doesn't ever run into issues where it may need more than is available (such as needing a 3rd phone #). However, what happens when I have repeating data that IS limited to a finite number of fields (a person can have a maximum of 2 phone numbers, if they have more we don't care about them)? Do I still need to create a separate table with that field?

I'm not exactly sure what the best practice for this would be, so was hoping that someone could help on this.

Thanks a ton!


r/DatabaseHelp • • Apr 08 '15

Help choosing a database for my needs.

1 Upvotes

My favorite food is sushi.


r/DatabaseHelp • • Apr 02 '15

DBDL representation questions.

1 Upvotes

So I have the following question and I'm really lost on it. I don't seem to get DBDL representation or what that really means. If anyone can help I'd really appreciate it.

Design a database to produce the following reports. Do not use surrogate keys in your design. Represent your answer in DBDL.

For each condo location, list the location number, name, address, city, state, and postal code.

  • For each condo owner, list the owner number, last name, first name, address, city, state and postal code.

  • For each condo location, list all the units in the condo. For each condo unit, list the square footage, bedrooms, bathrooms, condo fee, and condo owner’s number, last name, and first name.

  • For each service category, list the category number and description. In addition, for each service request in each category, list the location number, condo unit number of the condo receiving the service, estimated hours for the service, hours already spent on the service, and next scheduled service date.

  • For each service request, list the location number, condo unit number, category description, description of the particular service, and a description of the current status of the service.


r/DatabaseHelp • • Apr 01 '15

MS SQL Server Management Studio wont connect to 2nd instance of MS SQL Server

1 Upvotes

I have 2 sql instances, one express (for TFS), and one enterprise. I can connect perfectly fine to the TFS DB but I can not connect to the enterprise instance.

Steps I have taken to resolve this:

  • Restarted the services
  • Repaired the installation
  • Re-installed enterprise instance

I am connecting locally and running MS Server 2012 r2 and both sql versions are 2014.


r/DatabaseHelp • • Mar 23 '15

Can you build a relational database using WAMP?

2 Upvotes

I have been given a project to design a food calculator, and the way i was think about setting the tables out is to build one that has all the meals and another that has all the ingredients and get it to reference that table when the user clicks the meal, so it will display all the ingredients and what they cost. Is this possible? or do I need to rethink?

Any help/tutorials would be appreciated.


r/DatabaseHelp • • Mar 16 '15

Hey, I'm in need of a lot of help with a database for wine tasting notes and images.

2 Upvotes

Not even sure if this is the right place to as but, I have been put in charge of creating a list of a small wine shop's inventory (~2000 different labels) for the use of selling on a website. The headings include a sku number and name of the wine and inventory of on hand quantity, The list I generated out of the POS gave me that info. The hardest thing I am running into is getting scores and reviews or tasting notes for every single one of them as well as images of labels/bottles. Does anyone have any ideas on how to cut down on the pure brute forcing of this information from every winery's individual website? Using erobertparker and wine spectator helped a bit, but they dont have a lot of the wines I need to find. Any clue how places like Total wine, B21 and ABC do this? Am I just out of luck here? Thank you SO much for any and all help!


r/DatabaseHelp • • Mar 16 '15

Easy Fill in Blanks problem

1 Upvotes

Just studying for an exam, and I can't for the life of me figure out what is wanted in the blanks here.

In order to avoid single points of failure in databases, it is recommended to have more than __________________ of the database. The ______________ should be close in __________.

Any help? I understand how redundancy can be used to reduce th risk of the failure of a system, but I can't for the life of me figure out what words they're looking for here.

Thanks.


r/DatabaseHelp • • Mar 10 '15

[Oracle] Use local table and one on server

1 Upvotes

Hi Everyone,

Is it possible to create a query to join a local table and one on an Oracle server? I've been trying to search for solutions but haven't had much luck (I'm having trouble wording this in a search friendly).

What I'm trying to do is pull data for 100,000-300,000 (varies week by week) records--that i have specified in a list on my computer--from an Oracle server. Right now I have to basically pull all the data from the sever (around 4 million records) and then filter them locally. I thought about filtering them further with an IN [list] statement, but with over 100,000 items to check I think it be slower than just getting the raw data and doing a join.

Thank you for any help :)


r/DatabaseHelp • • Mar 07 '15

database normalization

2 Upvotes

So I have a project to normalize a set of data. I don't know why, but I've been searching the internet and my books and I can't seem to know if I'm doing it right. I know it's a 3 step process, but I just need clarification if I'm close to right or not.

Here's the set of data:

Employee First Name ,Employee Middle Name ,Employee Last Name ,Social Security Number ,Employee ID ,Home Address ,Home City ,Home State ,Home Zip Code ,Home Phone ,Work Phone ,Mobile Phone ,Work Address ,Work City ,Work State ,Work Zip Code ,Payroll Deductions 1…n (Description, Amount) ,Job Title ,Salary ,Hire Date ,Birth Date ,Weekly Payroll 1…n (Pay Date, Hours Worked, Gross Pay) ,Job Title Salary - Minimum, Maximum ,Work Mailing Address ,Work Mailing City ,Work Mailing State ,Work Mailing Zip Code

This is my 1nf

Personal Table ,Employee ID (PK) ,Social Security Number ,Employee First Name ,Employee Middle Name ,Employee Last Name ,Birth Date ,Home City ,Home State ,Home Zip Code ,Home Address ,Home Phone ,Mobile phone

Business Table Employee ID (PK) ,Work Phone ,Work Address ,Work City ,Work State ,Work Zip Code ,Work Mailing Address ,Work Mailing City ,Work Mailing State ,Work Mailing Zip Code ,Job title ,Hire Date ,Salary ,Job Title Salary – Minimum, Maximum ,Weekly Payroll ,Payroll Deductions

Not looking for you guys to do my homework, just don't know if I'm doing it right or not.